Add a table constraint and it's performance

I have a table with 100 gigs of data - and it seems it takes forever to add a
new constraint to this table, I finally have to cancel the statement after 1
hr of running. Is there a reason why or I am missing something.
below is my script
ALTER table [dbo].[acc_image]
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) < 2)
Label is the varchar of 50 and the fn_count_acc_image is to check the
duplicate label.
Thanks in advance.
It's quite normal. When you create a new check constraint all records in a
table are validated against this new constraint.
If you are sure that there are no records that violate this new constraint
(you can check this by writing the proper SELECT statement) use WITH NOCHECK
option:
ALTER table [dbo].[acc_image]
WITH NOCHECK
add constraint [chk_acc_image_table]
check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
2)
Regards
Pawel Potasinski
Uytkownik "PhilN" <philngu@.msn.com> napisa w wiadomoci
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>
|||Hi
Read this article
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
"PhilN" <philngu@.msn.com> wrote in message
news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>I have a table with 100 gigs of data - and it seems it takes forever to add
>a
> new constraint to this table, I finally have to cancel the statement after
> 1
> hr of running. Is there a reason why or I am missing something.
> below is my script
> ALTER table [dbo].[acc_image]
> add constraint [chk_acc_image_table]
> check ([label] <> '' and [dbo].fn_count_acc_image_labels] (acc_image_id) <
> 2)
> Label is the varchar of 50 and the fn_count_acc_image is to check the
> duplicate label.
> Thanks in advance.
>
|||Hi Uri,
The article is great, but if your primary goal is to prevent waiting at the
moment you use WITH NOCHECK anyway.
Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
Regards
Pawel Potasinski
Uytkownik "Uri Dimant" <urid@.iscar.co.il> napisa w wiadomoci
news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
> Hi
> Read this article
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
>
>
> "PhilN" <philngu@.msn.com> wrote in message
> news:F054C6E4-82B7-4848-9D04-CCBA24BFF8BE@.microsoft.com...
>
|||yes, this is exactly what I want to do - since I know 100% all rows in the
table are abide within this constraint. Using "with check" is not very
efficient in my case.
Beside with the table size that i have, the alter table took a whole night
(> 8 hr) to run and it lock up anyone who try to access the table. I have to
aborted the operation in the morning.
Thanks very much Pawel and everyone who made the suggestions.
"Pawel Potasinski" wrote:

> Hi Uri,
> The article is great, but if your primary goal is to prevent waiting at the
> moment you use WITH NOCHECK anyway.
> Then you can go with WITH CHECK CHECK CONSTRAINT in the maintenence window.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa3 w wiadomo?ci
> news:OjnLxaEyHHA.988@.TK2MSFTNGP02.phx.gbl...
>
>