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

PDinCA

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...

peter.peart

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:

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:

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

PDinCA

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...

peter.peart

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