by Kalen Delaney via Kalen Delaney on 1/23/2009 10:32:00 PM
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
Original Post: Did You Know? Management Studio Got Smarter!
The content of the postings is owned by the respective author. SQL Feeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on SQL Feeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
