Prev: Distinct Column Level Output
Next: String Manipulation: REPLACE multiple variables at once through a Join: How to
From: hayko98 on 14 Dec 2009 17:42
Hi.
I am inserting data into 3 tables. Before I insert, I am dropping some
of the constrains. After inserting the data I added dropped
constrains back .Most of the constraints that I added I did not
specify WITH CHECK or WITH NOCHECK.5 Of them I used WITH NOCKECK.My
question is how would this adding and dropping constrains effect to
indexes and performence?.Do I have to reindex them?


Thank you
From: Erland Sommarskog on 14 Dec 2009 17:54
hayko98 (vardan.hakopian(a)gmail.com) writes:
> I am inserting data into 3 tables. Before I insert, I am dropping some
> of the constrains. After inserting the data I added dropped
> constrains back .Most of the constraints that I added I did not
> specify WITH CHECK or WITH NOCHECK.5 Of them I used WITH NOCKECK.My
> question is how would this adding and dropping constrains effect to
> indexes and performence?.Do I have to reindex them?

When it comes to UNIQUE and PRIMARY KEY constraints, NOCHECK has no
effect.

For FOREIGN KEYS and CHECK constraints, using NOCHECK means that SQL
Server does not verify that data in the table complies with the constraint.
This has the implication that the optimizer cannot trust the constraint,
and this could have an impact on performance.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 15 Dec 2009 21:15
>> My question is how would this adding and dropping constraints effect to indexes and performance?. <<

This is not the problem. You will eventually have no data integrity
if you keep doing this. This is like thinking you can improve gas
mileage by making the car lighter -- by removing all the safety
features.
 | 
Pages: 1
Prev: Distinct Column Level Output
Next: String Manipulation: REPLACE multiple variables at once through a Join: How to