April 21, 2011 at 10:42 am
Hi there,
I am having a huge database with many constraint and i have updated some data disabling constraint and I j't want to enable them back.
I have used below query
EXEC sp_msforeachtable 'ALTER TABLE MyTable NOCHECK CONSTRAINT all'
exec sp_msforeachtable @command1='print ''MyTable''', @command2='ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT all'
But when I run enable query it take long time due to huge volume of data. It take more than 1 hour.
I jt want to enable it quickly and proper way without having any issue.
Please guide me how to overcome this issue.
April 21, 2011 at 10:48 am
You already have the proper way. You can validate to make sure you have all the indexes in place to make sure this runs as fast as possible.
Other than that you'll have to run that off hours.
Plan Z would be to reenable the constraint but without validating the data. This can leave you with bad data and / or bad execution plans killing performance anyways.
April 21, 2011 at 10:56 am
Thank you for quick reply.
Year that sound is better. I can guarantee the data. J't tell me the way enableing constraint without validating data?
April 21, 2011 at 11:10 am
Anushka Udayanga Ruwan Pathirana (4/21/2011)
Thank you for quick reply.Year that sound is better. I can guarantee the data. J't tell me the way enableing constraint without validating data?
ALTER TABLE dbo.t2 WITH NOCHECK CHECK CONSTRAINT FK_t2_t1;
Here's a good explaination of what's going on :
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/907e1139-6b9d-4707-a279-d8330e238a8c
April 21, 2011 at 3:06 pm
Grate!! it works, thank you very much.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply