THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? Management Studio Got Smarter!

 

Hopefully you're aware that there is no way using the ALTER TABLE statement to add a new column in a particular ordinal position within a table. I see people in the forums asking for this capability, and they may point out that it is possible to do this using the Table Designer in Management Studio. However, usually someone will point out to them that you're really not altering the table in that case, you're actually creating a whole new table. And if it's a BIG table, it can take a long time, because all the data has to be moved to the table with the new definition, and then all the indexes have to be rebuilt. You can do the same using TSQL statements, but it's not a single quick and fast operation.

I just discovered today that SQL Server 2008 Management Studio is a bit smarter about this. You are actually not allowed to save a change to a table that will require the table be rebuilt. You can, however, change that behavior to allow the change, as in older versions. I also discovered that this new behavior had already been discovered, and blogged about, by the awesome Brian Knight at

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

Brian shows you the error message, and the the dialog to change the behavior.

So after reading Brian's post, I did some quick tests to verify that after changing the option, I could indeed make changes to a table that required a rebuild.

Then when I was getting ready to blog this, I thought I would generate my own screenshot of the error message. So I used Table Designer to remove the identity property from the  AdventureWorks.Sales.SalesOrderHeader.SalesOrderID column and tried to save the table. I received a warning, but not the one I expected.  It told me that half a dozen other tables would have to be changed because of my change to SalesOrderHeader. I presumed the message about not be able to actually save the change that required rebuilding the table would come next... so I clicked OK. Oops, it didn't complain. Because I had turned off the option to  prevent saving such changes, and I never turned it back on again.

Wasn't there a thread going around a while ago about goofs people had made?

Oh, well. At least I have the ability to re-create AdventureWorks.

 

Have fun!

~Kalen

Published Friday, January 23, 2009 3:32 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kalen Delaney said:

I just realized that I should have ended this by saying that although Management Studio obviously is smarter, I can't say the same thing about myself! :-)

~Kalen

January 23, 2009 5:49 PM
 

Rajib Bahar said:

Thanks for sharing this. I was able to reproduce this issue. It appears we can add column only at the last position in the table. Brian's pointer did help me change it back to old behavior... I am guessing there were some horror stories around the re-creation process.

January 23, 2009 6:37 PM
 

Kalen Delaney said:

I think the new behavior is a definite improvement. There were way too many horror stories from people not realizing that many schema changes required that the whole table be rebuilt.

~Kalen

January 23, 2009 6:55 PM
 

Piyush Goriya said:

Thank to submit this nice story...

January 26, 2009 5:20 AM
 

Vern Rabe said:

I would like to see an option in SSMS that would turn off execution behind the scenes and only allow script creation, so the DBA would always know what steps were necessary to implement the change. As it is, if you configure SSMS to prevent table re-creation statements, you not only can’t have SSMS run the script for, you can’t even create the script for manual execution.

January 26, 2009 1:47 PM
 

Michael Bourgon said:

Kalen, an OffTopic question: is it still possible to buy your SQL Server DVD?  I went to what I thought was the web site, but all that's there is some sort of wordpress site template.

January 26, 2009 3:12 PM
 

Dinakar said:

Unfortnately, its a client tools setting. It will be hard to enforce this across the DBA group. Although only people with permissions would be able to do this, it would be nice if this feature can be controlled via sp_configure.. something like "allow schema changes through SSMS GUI".  

January 27, 2009 11:31 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement