Can you add a constraint using the below syntax but to check whether a field
has only numeric digits?
So that if Threshold was updated to say 'marc1' it would fail.
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))Something like this?
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(Threshold) = 1)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"marcmc" wrote:
> Can you add a constraint using the below syntax but to check whether a fie
ld
> has only numeric digits?
> So that if Threshold was updated to say 'marc1' it would fail.
> ALTER TABLE misre_threshold WITH NOCHECK
> ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))|||Try this:
...check (<column name> not like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||thanks guys BUT
I tried the following:
but the Threshold Column is dataType money
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(convert(money,Threshold)) =
1)
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (Threshold not like '%[^0-9]%')
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.|||Aha, I assumed it was a character column (you haven't mentioned the actual
data type).
Use CAST in the constraint:
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||By the way - have you checked out this nice article regarding the ISNUMERIC
function?
http://www.aspfaq.com/show.asp?id=2390
ML
http://milambda.blogspot.com/|||thanks that does build it but my .net application runs as follows and so
encounters an error, should i change my .net app? I would rather not.|||Thanks for that..
Kewl article.. But I am using 2000 SP4 and the results are different. But
some misses nevertheless.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"ML" wrote:
> By the way - have you checked out this nice article regarding the ISNUMERI
C
> function?
> http://www.aspfaq.com/show.asp?id=2390
>
> ML
> --
> http://milambda.blogspot.com/|||Whenever a constraint is violated the SQL Server returns an error saying so,
and the error should be handled by the calling application. Isn't the
constraint already implemented on the application tier? Why not?
What exactly do you need?
An alternative is to use a trigger and simply rollback any transactions that
would otherwise violate the constraint, but this is IMHO a kludge, since the
data would not be inserted and the user would not be notified of this - in m
y
view - vital consequence.
ML
http://milambda.blogspot.com/|||I see your point.
I don't know what your exposure is to validating data on a datagrid within
.net but it aint pretty.
sample ddl,
What I want is the first update to break the constraint, the second should
not.
create table marc(
ThresholdType varchar(6),
Threshold money)
insert into marc values('RecEst', 20000.00)
ALTER TABLE marc WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
select * from marc
update marc set Threshold = 'marc1' where ThresholdType = 'RecEst'
update marc set Threshold = 20001.00 where ThresholdType = 'RecEst'
Showing posts with label constraint. Show all posts
Showing posts with label constraint. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Add UNIQUE constraint
Hi,
I want to add unique contraint to EXISTING column in EXISTING table.
These articles really helped me.|||
I want to add unique contraint to EXISTING column in EXISTING table.
I have ms sql 2005.
How to do that?
If you have ms SQL Express 2005, then you should probably download and install SQL Server Management Studio Express. Once that's done, it is easy - attach the database file to SQL Express, then add the unique constraint there.
Careful if you are coming from an MS Access world. In MS Access, you can specify a unique constraint that will ignore NULL values. In that case, the constrained column will be unique only if the value is not null. You can have many entries where the value is null. If that is an issue, then there is another way to do it using a Schema-bound view that selects only non-null entries from the original table and applies a unique index to the entry in the view.
You will also find an excellent reference in the SQL Books Online, which you can download and install on your machine.
Hope this helps,
Flavelle
|||I have SQL Standrard. I read books online.I need exact steps, I cannot understand Books.
Can you please provide me with steps or procedure that would do that?|||
give me a little time, and I'll come up with a couple of sample scripts that you can run.
Flavelle
|||Ok, I'm waiting.Just give me the simplest script, or explain how to set it in Studio.|||
fballem:
If you have ms SQL Express 2005, then you should probably download and install SQL Server Management Studio Express. Once that's done, it is easy - attach the database file to SQL Express, then add the unique constraint there.
Careful if you are coming from an MS Access world. In MS Access, you can specify a unique constraint that will ignore NULL values. In that case, the constrained column will be unique only if the value is not null. You can have many entries where the value is null. If that is an issue, then there is another way to do it using a Schema-bound view that selects only non-null entries from the original table and applies a unique index to the entry in the view.
You will also find an excellent reference in the SQL Books Online, which you can download and install on your machine.
Hope this helps,
Flavelle
In SQL Server you can create a Unique Index with the IGNORE_DUP_KEY option and duplicates will not be inserted. Unique constraints are nullable while Unique indexes are not. Try the link below for step by step of how to create Unique Constraint with the GUI in management studio. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueindex.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueconstraint.asp
|||Thanks a lot.
These articles really helped me.|||
And if you wanted to do it via script, here are two versions:
This script, applied to a table, will result in a unique index. If the column allows Null, then only one row can contain Null. Replace TestTable with the name of your table and TestColumnUnique with the name of the column:
ALTER TABLE [dbo].[TestTable]ADD CONSTRAINT [UQ_TestTable_TestColumnUnique]UNIQUE NONCLUSTERED ([TestColumnUnique]ASC)WITH (PAD_INDEX =OFF, SORT_IN_TEMPDB =OFF, IGNORE_DUP_KEY =OFF, ONLINE =OFF)ON [PRIMARY]
The following two scripts will result in a schema-bound view with an index:
The first script will create the schema bound view. Note that TestView is the name of the view, TestTable is the name of the table from which the data will be obtained and TestColumnNonNullUnique is the name of the column to which you want to apply the constraint. Note that only non-null values are included in the view:
CREATE VIEW [dbo].[TestView]
WITH SCHEMABINDING
AS
SELECT ID, TestColumnNonNullUnique
FROM dbo.TestTable
WHERE (TestColumnNonNullUniqueISNOT NULL)
GO
CREATE UNIQUE CLUSTERED INDEX [UQ_TestView_TestColumnNonNullUnique]ON [dbo].[TestView] ([TestColumnNonNullUnique]ASC)WITH (PAD_INDEX =OFF, SORT_IN_TEMPDB =OFF, DROP_EXISTING =OFF, IGNORE_DUP_KEY =OFF, ONLINE =OFF)ON [PRIMARY]
Hope that this is the final piece to the puzzle. I wish that I could take credit for this approach, but another member of the forum was kind enough to provide me with the solution, so I am happy to pass it on.
Regards,
Flavelle
Saturday, February 25, 2012
Add Foreign Key Constraint in filegroup
Hi,
I'd like to create a foreign key constraint and store it in a different
filegroup than "PRIMARY"
For this, I've created a new filegroup named "INDEX"
This is my script :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
(
[FIL_IDFOLDER]
) REFERENCES [dbo].[T_FOLDER] (
[FOL_IDFOLDER]
) ON DELETE CASCADE
ON [INDEX]
GO
And it doesn't work !
But what is very strange, is that I can create a primary key constraint
correctly on this filegroup :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
(
[FIL_IDFILE]
) ON [INDEX]
GO
And this works !
It would be great if someone can telle me where my error is !
ThanxFilegroups are containers for storing data. Data are either data pages or in
dex pages. The reason
you can specify a filegroup when you define a primary key is that it automat
ically creates an index
for you,. so the file group you specify defines where that index will be sto
red. A foreign key
doesn't come with an index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jerome.avoustin@.gmail.com> wrote in message
news:1137404237.380973.84080@.g43g2000cwa.googlegroups.com...
> Hi,
> I'd like to create a foreign key constraint and store it in a different
> filegroup than "PRIMARY"
> For this, I've created a new filegroup named "INDEX"
> This is my script :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
> (
> [FIL_IDFOLDER]
> ) REFERENCES [dbo].[T_FOLDER] (
> [FOL_IDFOLDER]
> ) ON DELETE CASCADE
> ON [INDEX]
> GO
> And it doesn't work !
> But what is very strange, is that I can create a primary key constraint
> correctly on this filegroup :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
> (
> [FIL_IDFILE]
> ) ON [INDEX]
> GO
> And this works !
> It would be great if someone can telle me where my error is !
> Thanx
>
I'd like to create a foreign key constraint and store it in a different
filegroup than "PRIMARY"
For this, I've created a new filegroup named "INDEX"
This is my script :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
(
[FIL_IDFOLDER]
) REFERENCES [dbo].[T_FOLDER] (
[FOL_IDFOLDER]
) ON DELETE CASCADE
ON [INDEX]
GO
And it doesn't work !
But what is very strange, is that I can create a primary key constraint
correctly on this filegroup :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
(
[FIL_IDFILE]
) ON [INDEX]
GO
And this works !
It would be great if someone can telle me where my error is !
ThanxFilegroups are containers for storing data. Data are either data pages or in
dex pages. The reason
you can specify a filegroup when you define a primary key is that it automat
ically creates an index
for you,. so the file group you specify defines where that index will be sto
red. A foreign key
doesn't come with an index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jerome.avoustin@.gmail.com> wrote in message
news:1137404237.380973.84080@.g43g2000cwa.googlegroups.com...
> Hi,
> I'd like to create a foreign key constraint and store it in a different
> filegroup than "PRIMARY"
> For this, I've created a new filegroup named "INDEX"
> This is my script :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
> (
> [FIL_IDFOLDER]
> ) REFERENCES [dbo].[T_FOLDER] (
> [FOL_IDFOLDER]
> ) ON DELETE CASCADE
> ON [INDEX]
> GO
> And it doesn't work !
> But what is very strange, is that I can create a primary key constraint
> correctly on this filegroup :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
> (
> [FIL_IDFILE]
> ) ON [INDEX]
> GO
> And this works !
> It would be great if someone can telle me where my error is !
> Thanx
>
Thursday, February 16, 2012
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...
>
>
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...
>
>
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_ima
ge_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_korne.../>
raints.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_ima
ge_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_korne...
straints.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 th
e
> 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...
>
>
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_ima
ge_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_korne.../>
raints.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_ima
ge_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_korne...
straints.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 th
e
> 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...
>
>
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
U¿ytkownik "PhilN" <philngu@.msn.com> napisa³ w wiadomo¶ci
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
U¿ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa³ w wiadomo¶ci
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...
>>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.
>|||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> napisa³ w wiadomo¶ci
> 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...
> >>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.
> >>
> >
> >
>
>
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
U¿ytkownik "PhilN" <philngu@.msn.com> napisa³ w wiadomo¶ci
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
U¿ytkownik "Uri Dimant" <urid@.iscar.co.il> napisa³ w wiadomo¶ci
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...
>>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.
>|||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> napisa³ w wiadomo¶ci
> 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...
> >>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.
> >>
> >
> >
>
>
Subscribe to:
Posts (Atom)