WITH (NO)CHECK .. NOT FOR REPLICATION Treated the SAME!

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

WITH (NO)CHECK .. NOT FOR REPLICATION Treated the SAME!

Postby PDinCA » Sat Oct 23, 2010 1:24 am

I want to create my FK constraints WITH CHECK and include the NOT FOR REPLICATION phrase.

Whatever I try, SSMS insists on WITH NOCHECK and an ALTER-CHECK immediately following it whenever I script the constraint. I'm assuming SSC and SQL Compare \"see\" things the way Management Studio presents them...

Why am I bothered by this? Because every CONSTRAINT that was WITH CHECK in any database linked into SSC now sees a DIFF on constraints that were defined sans WITH (NO)CHECK vs. the linked-to source code in SVN, which appears to have been added with WITH NOCHECK irrespective of the actual constraint definition in the database. If I EXPLICITLY drop and add the constraint WITH CHECK or sans WITH CHECK/NOCHECK, SSC Commit sees no change! but the \"changed icon\" (blue blob) is turned on as soon as I DROP-CREATE with a different constraint WITH clause.

Wassup? Maybe I'm simply missing something I shouldn't care about...
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby peter.peart » Tue Oct 26, 2010 1:51 pm

Hi there,

Thanks for your post.

I have been looking into this and just wanted to confirm that i am replicating what it is you are reporting. I am first of all using the below script to create a table with a check constraint not for replication:

Code: Select all
CREATE TABLE dbo.test
(
abc int NOT NULL
) ON [PRIMARY]

go
ALTER TABLE dbo.test ADD CONSTRAINT
CK_test CHECK NOT FOR REPLICATION ((abc>0))


Now, if I then use SSMS to script this out, I get the following code:

Code: Select all
CREATE TABLE [dbo].[test](
   [abc] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[test]  WITH NOCHECK ADD  CONSTRAINT [CK_test] CHECK NOT FOR REPLICATION (([abc]>(0)))
GO

ALTER TABLE [dbo].[test] CHECK CONSTRAINT [CK_test]
GO


When however I manually check in the original code to SVN and then perform a drop and re-create on the original table in SSMS, SQL Source Control initially reports a blue blob and then confirms that there is no object differences.

Is that correct? If that's the case, then essentially what is happening is the blue blob is appearing because at the basic level we are detecting that there is a change, but we don't know what it is (because an actual comparison hasn't been performed) and most changes are legitimate. When however you actually bring up the Source Control tab, we are noticing that there isn't actually a difference hence the blue blob then disappearing.

Pete
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby PDinCA » Tue Oct 26, 2010 5:55 pm

You are correct on all points. This is exactly what I'm seeing. I'm guessing that the way SSMS intervenes means it's actually pointless creating a constraint WITH CHECK as SSMS will always script it as a 2-part NOCHECK then ALTER-CHECK...
PDinCA
 
Posts: 512
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby peter.peart » Wed Oct 27, 2010 2:48 pm

Hi there,

Thanks for your repl; I am glad I understood correctly.

Essentially, AFAIK this behaviour is inherently down to the internals of SQL Server and at what point the constraint is applied to the data.

For example, if you were performing an alter table statment to include check, there is some magic that SQL Server performs to ascertain whether CHECK is applied to the table as a whole, i.e. including existing data or to just apply the check to new data added.

To be honest though, I am not sure how SQL Server determines this and whether it is behaviour that you can alter.

HTH!

Pete
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids


Return to SQL Source Control 1

Who is online

Users browsing this forum: No registered users and 1 guest