Script Generator in table designer?

Question

Script Generator in table designer?

Ncage

SSChasing Mays

Points: 638
More actions
August 18, 2014 at 9:39 am

#292245

I changed some indexes in our development database and saved to a script using the script generator in the table designer. Once one of our DBAs reviewed the script he had a problem with what was written. Most of the alter statements look like the following:
ALTER TABLE dbo.GenPhoneLogRec WITH NOCHECK ADD CONSTRAINT
MyFK1 FOREIGN KEY
The highlighted "WITH NOCHECK" above is what he had the issue with.
Here is the comment he wrote:
when you are re-creating the FKís, you are doing so using the 'WITH NOCHECK' option. That will cause the query optimizer to NOT consider these FKís during plan creation, since they are untrusted; e.g., their contents have not been verified. Change the option to 'WITH CHECK'.
My question if its such a bad thing then why is the sql server script generator doing it? Here is the version of sql server we are using: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4321.0 (X64). I used SSMS 2012 to generate the scripts though.
I'm trying to prevent having to dig into every script sql server generates and change them :).
thanks...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

The reason it includes NOCHECK is to ensure that the index would get created. It might not be of much use, but imagine the complaints if the script was unable to create the index. Yes, such complaints are foolish. No, there's not much you can do about stupid - it's not fixable. I don't necessarily agree with every choice made by MS, but in this case, I agree with you about this specific problem. However, if you were hoping to generate scripts from SSMS that would never need modification, I have to actually start worrying. The scripts SSMS can generate are suggestions, and obviously, MS can't know in advance what you want to do, so they create a script in this case that will at least go ahead and create the object, and leave it to you to be responsible for getting that check to occur. At least that way, responsibility is where it belongs. It's not the best choice, but it probably avoids more grief for MS than it causes for MS. This might not be the answer you wanted, but it's all there is...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 2

If I'm not mistaken, the script generator is including that in the ALTER statements because the FK was created without the "WITH CHECK" option. I am fairly certain any FK that was created with that clause will be generated with that clause.

In fact, I just tested it.

CREATE TABLE BrandieTest (MyID INT IDENTITY(1,1), Name VARCHAR(20),

AddressID INT NOT NULL)

ALTER TABLE dbo.BrandieTest WITH CHECK ADD CONSTRAINT MyFK1 FOREIGN KEY (AddressID)

REFERENCES dbo.RandomTable(RandomID);

When I went in and scripted out only the foreign key via the GUI, I got:

ALTER TABLE [dbo].[BrandieTest] WITH CHECK ADD CONSTRAINT [MyFK1]

FOREIGN KEY([AddressID]) REFERENCES [dbo].[RandomTable] ([RandomID])

GO

So the next time he complains about your lack of "WITH CHECK", mention to him that if the FKs had been created correctly in the first place, you wouldn't have to go around updating every single last one of them with the proper code.

EDIT: Made code more readable so you wouldn't have to scroll right.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

The DBA's quite right, you need "WITH CHECK" explicitly specified (or allowed to default). I don't see an option in scripting to explicitly specify this (at least through 2008 R2 SSMS).

I strongly suspect developers may be responsible for disabling the constraint. I've seen many cases where they disable the constraint to do a big load, then re-enable it afterward ... but not properly, leaving the constraint untrusted.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Ncage SSChasing Mays Points: 638 More actions · Answer 4

Thanks everyone for all the help. That was definitely the problem (untrusted) in our database. The script generator was just putting the database to the state that it was in. I end up doing a query on our prod database and we had over 600 untrusted foreign keys and 53 that were disabled.