SQL Server Check/NoCheck difference in generated scripts

Question

I am trying to sync up the schemas between to different databases. Basically, I ran tasks->Generate Scripts with SQL Server Management Studio (2005) on both databases and am comparing the output with a diff tool.

For some reason, one script adds the constraint WITH CHECK and one WITH NO CHECK, followed by both constraints being re-enabled.

I for the first database I get:

ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT [FK_Profile_OrganizationID] FOREIGN KEY([OrganizationID])
REFERENCES [dbo].[Organization] ([OrganizationID])
GO
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID]
GO

The second database generates as

ALTER TABLE [dbo].[Profile]  WITH NOCHECK ADD  CONSTRAINT [FK_Profile_OrganizationID] FOREIGN KEY([OrganizationID])
REFERENCES [dbo].[Organization] ([OrganizationID])
GO
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID]
GO

So I have two questions:

1. Is the end result the same? (Edit: It seems that a lot of people are picking up on only the first statement of the two scripts. I am interested in the end result of the entirety of both scripts.)

2. If the end result is the same, why does Management Studio generate them differently for different databases?

Answer 1

Yes they the two scripts are different

WITH CHECK will check existing data against the new constraint.
WITH NOCHECK will not check existing data against the new constraint. This will allow you to have child records without a corresponding parent.

EDIT: As for why SSMS is doing this I have no idea

Answer 2

Both are SQL Server 2005 servers? As the result is the same, the code generation tool maybe use different routines based in different versions of the product

Answer 3

The end result is not the same!

SQL Server will not trust the uniqueness of the FK is it is not checked. This means additional processing is required if you use the column in a query.
Long story short is that you should get SQL Server to check the column so it's considered trusted.

As for why they're different from different servers, check the isnottrusted column in sys.foreign_keys. This may affect what SSMS is generating?

For more of a rant on this, check my other answer that relates to FK & NO CHECK/ CHECK options.