Do pre-defined joins in SQL Server slown down inserts/deletions?

I wanted to know if SQL Server 2000 does something behind the scenes in a transparent manner whenever records are inserted/deleted from two tablesbetween which a join is defined based on a primary key to foreign key relationship. So I have already defined a parent-child relationship through the 'Database Diagram' between these 2 tables. I know when a table is indexed then SQL Server will perform some actions behind the scenes in a transparent manner.
The reason I am asking this question is to know if its bad to define parent-child relatioship between 2 tables that will each contain thousands or millions of records.
Hi,
I do not know that is the exact control structure for an insert but ifyou have some constraints on your columns as well as foreign keys, therecord you are trying to insert is controlled by means of thoseconstraints.
For example, you have a foreign key on CityId column referencing Citiestable. Then the column value you are inserting for CityId must bepresent at Cities table. So this is a job to check if the cityid reallyexists in Cities table. Perhaps as simple as select * from cities wherecityid = @.cityid . But this brings an extra work during the insert.
This causes a delay. But maintains data integrity within your database.And data integrity is more important for me. Because you can improveperformance by many methods (by improving hardware, sql code, sqlserver parameters,etc) but if you lose your data integrity, it will notbe easy perhaps impossible to maintain it back.

Indexes are very useful. But if you use so many indexes on a tableespecially which is used only for insert and updates (not for so muchread), every insert will mean a new update or insert on each index usedon that table.
In short, we have the plus and minus side by side. The outcome depends how and where you use them.
Eralper
http://www.kodyaz.com

|||EDIT
I don't think DRI(declarative referential integrity) will slow down inserts and deletes because one thing it is not insert related it just guarantees updates and deletes will cascade up or down based on what you set up.I am assuming you marked the insert relationship also but that is just for data integrity by enforcing the relationship between tables. It is ANSI SQL if you say cascade on delete no action it will not affect the other table but if you say on delete cascade then it will affect the other table. If your tables are too big you should look into creating views that will be smaller. Run a search for cascade delete and cascade update in SQL Server BOL (books online). Hope this helps.sql

Do backups need to be manually deleted?

If I create a backup plan with weekly full, daily differential, and hourly
log backups to local disk, will the backup sets continue to grow over time
or do they get overwritten? IOW, after six months will I have THOUSANDS of
backup files that will need to be deleted manually?
New backups are created each time. You can configure the maintenance plan
to delete old backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bill Fuller" <someone@.nospam.com> wrote in message
news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
If I create a backup plan with weekly full, daily differential, and hourly
log backups to local disk, will the backup sets continue to grow over time
or do they get overwritten? IOW, after six months will I have THOUSANDS of
backup files that will need to be deleted manually?
|||Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
this?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> New backups are created each time. You can configure the maintenance plan
> to delete old backups.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
> If I create a backup plan with weekly full, daily differential, and hourly
> log backups to local disk, will the backup sets continue to grow over time
> or do they get overwritten? IOW, after six months will I have THOUSANDS of
> backup files that will need to be deleted manually?
>
|||When you specify a backup directory, there is also a checkbox to Remove
files older than a certain period.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bill Fuller" <someone@.nospam.com> wrote in message
news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
this?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> New backups are created each time. You can configure the maintenance plan
> to delete old backups.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
> If I create a backup plan with weekly full, daily differential, and hourly
> log backups to local disk, will the backup sets continue to grow over time
> or do they get overwritten? IOW, after six months will I have THOUSANDS of
> backup files that will need to be deleted manually?
>
|||Bill,
And to add to Tom's comment, it sounds like you are running SQL Server 2000,
but if you are running SQL Server 2005 there is also a Maintenance Cleanup
Task that deletes old files.
RLF
"Bill Fuller" <someone@.nospam.com> wrote in message
news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
> this?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
>
|||I am running SQL Server 2005 and think I found that task... it defaults to 4
weeks, which I kept.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:um2NmS7UIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Bill,
> And to add to Tom's comment, it sounds like you are running SQL Server
> 2000, but if you are running SQL Server 2005 there is also a Maintenance
> Cleanup Task that deletes old files.
> RLF
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
>
sql

Distribution database in suspect state

I have sqlserver2000 and I have set up replication. Accidentally I
have deleted distribution data and distribution database went to
suspect state, Is there any way I can recover from this state?
Put the database into emergency mode and bcp the data out. Drop it, recreate
it and then bcp it back in.
Drop it using
sp_dropdistpublisher with no_checks and ignore_distributor,
sp_dropdistributiondb and sp_dropdistributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ST" <sitatetali@.gmail.com> wrote in message
news:1173548966.133736.226600@.v33g2000cwv.googlegr oups.com...
>I have sqlserver2000 and I have set up replication. Accidentally I
> have deleted distribution data and distribution database went to
> suspect state, Is there any way I can recover from this state?
>