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 Inserts into Temptable enforce order of select?

I have a temp table which I am trying to do a select INTO. The temptable has an identity field which increments by one. In the select query, I am doing an order by. It does not appear that the temp table retains the physical ordering of the records from the order by. When I do a select * FROM temptable without an order by, the first record that is displayed as an id of 113. Then about the 42nd record in the temp table is the ID=1 record. In addition, the table records are not ordered by the original order of the select that inserted into the temp table.

From this I am led to believe that SQL does not enforce the orders of records on INSERT into statesments. Is this a correct assumption, or might there be something else going on?Please take a look at the blog post below:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Ordering is not guaranteed for SELECT queries unless you include an explicit ORDER BY in the outer-most query. Similarly, the order of insertion of rows is not guranteed either. The identity values are however generated based on the order specified in the ORDER BY in INSERT...SELECT statement.

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

distributed transaction error - help urgent

i have two remote servers connected to each other through the linked servers.

i can run querries or even perform inserts as far as its not inside a transaction
but when i am trying to run distribted transaction but it keeps on returning the same msg.

Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

i have configured the firewall, made sure the DTC service is started on both and enabled. they both have the same Sp packs, same config.http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20888629.html

Go Google the error...there are probably a lot of reasons...|||1)are u using Microsoft Windows Server 2003 as OS? if it is,check this site,

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql