Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Be Cautious Altering Tables Using SQL Server Management Studio SSMS

By:   |   Read Comments (1)   |   Related Tips: More > SQL Server Management Studio

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


Often times we use the SQL Server Management Studio GUI to perform simple tasks such as altering a column in a table. However, you should always check your scripts before blindly performing the actions recommended by SSMS, because they may not always be, and are often not, the best way to perform actions within SQL Server.


Let's take a look at a simple task of resizing a column in the AdventureWorks database. If you need to change the NationalIDNumber in the HumanResources.Employee table, you can simply run the following script.



TABLE HumanResources.Employee

Now, let's perform the same task using SSMS. You can right-click the HumanResources.Employee table and select Design from the context menu to display the Table Designer. If you change the column length and generate the change script, you will end up with a script that contains 443 lines of code and involves creating a temp table with the new definition, inserting all the data from the old table, dropping the old table, and then renaming the temp table. If this operation is performed on a large table, what would have been a quick change can now take several hours.

SQL Server 2008 Management Studio

SQL Server 2008 Management Studio has an option on by default that prevents operations that are required drop and create tables; however, this option can be, and often is, turned off. Sometimes the best way to perform certian operations is by creating and populating a new table, you just need to be aware of what is about to happen and make sure you are okay with it first.

Next Steps

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, July 20, 2012 - 5:33:52 AM - Min Thu Kyaw Back To Top

Hi Simmons,

Thanks for Article . I 've learned something from this article.I think,Microsoft fixed this issue. I tried that It says that 

'Result' table

- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

I cant see that kinda script anymore if used 2008 SSMS. 


Learn more about SQL Server tools