Data Management - Latest Comments on SQL Advent 2012 Day 5: Do not trust the SSMS designers

SQLDenis [Member] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

SQLDenis [Member] — Sun, 09 Dec 2012 10:10:19 +0000

You know how many times I have answered how to turn that behavior off? Take a look at Stackoverflow how many times this question has been asked: http://stackoverflow.com/search?q=Prevent+saving+changes+that+require+table+re-creation&submit=search

I don't think these people once they turn it off realize what happens next time they do a change where SSMS just goes ahead and recreates the table

ta.speot.is [Visitor] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

ta.speot.is [Visitor] — Sun, 09 Dec 2012 03:15:50 +0000

Unless I'm mistaken, you can't make changes through the designer that involve dropping and recreating a table unless you opt into doing so. Out of the box, SSMS will not allow you to do it.

The "Prevent saving changes that require table re-creation" option is enabled by default and the limitations and risks of turning it off are pretty well documented:

http://support.microsoft.com/kb/956176

Paul Hunter [Visitor] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

Paul Hunter [Visitor] — Wed, 05 Dec 2012 21:02:14 +0000

Interesting post for finding "unnamed" constraints.

For SQL 2005+ you can use the system catalogs. For example:

select * from sys.default_constraints
where is_system_named = 1

There are views for: foreign_keys, check_constraints, and key_constraints.

SQLDenis [Member] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

SQLDenis [Member] — Wed, 05 Dec 2012 13:45:47 +0000

Paul, you are correct that you can create randomly named constraints in T-SQL as well, see this post here: How To Name Default Constraints And How To Drop Default Constraint Without A Name In SQL Server, it explains how you can find these 'unnamed' constraints

Paul Hunter [Visitor] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

Paul Hunter [Visitor] — Wed, 05 Dec 2012 13:39:23 +0000

One thing I notice is that non-SQL folk can use T-SQL to create defaults and checks, but not give them names...

ALTER TABLE my_table ADD new_column INT NOT NULL DEFAULT (0) WITH VALUES;

SQL Server will then assign a random name as it does with the designer.

I like having properly defined names for default, check and foriegn key constraints -- well all SQL objects. Everyone should create and publish a standards document that specifies how to properly name objects and a whole lot of other stuff. You'll end up with a lot less confusion for everyone concerned.

SQLDenis [Member] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

SQLDenis [Member] — Wed, 05 Dec 2012 13:21:59 +0000

sqlsister, I agree, this also makes it much easier to recreate the DB from scratch as well

sqlsister [Member] in response to: SQL Advent 2012 Day 5: Do not trust the SSMS designers

sqlsister [Member] — Wed, 05 Dec 2012 13:19:54 +0000

Also, changes to the database struture should be treated like all code and put under source control so they can easily be deployed. Writing a script to put into your source control system is one of the best ways to do this.