Red Gate forums :: View topic - WITH (NO)CHECK .. NOT FOR REPLICATION Treated the SAME!
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 1
SQL Source Control 1 forum

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

Search in SQL Source Control 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
PDinCA



Joined: 25 Jul 2005
Posts: 512
Location: Costa Mesa, CA, USA

PostPosted: Sat Oct 23, 2010 1:24 am    Post subject: WITH (NO)CHECK .. NOT FOR REPLICATION Treated the SAME! Reply with quote

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...
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Tue Oct 26, 2010 1:51 pm    Post subject: Reply with quote

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:
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:
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
Back to top
View user's profile Send private message Send e-mail
PDinCA



Joined: 25 Jul 2005
Posts: 512
Location: Costa Mesa, CA, USA

PostPosted: Tue Oct 26, 2010 5:55 pm    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Wed Oct 27, 2010 2:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group