Problem
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.
Solution
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.
USE
AdventureWorks
GO
ALTER TABLE HumanResources.Employee
ALTER COLUMN NationalIDNumber NVARCHAR(20) NOT NULL
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 has an option on by default that prevents operations that are required drop and create tables
Next Steps
- Check out some Best practices for SQL Server database ALTER table operations
- Read the tip Error ‘Saving changes is not permitted.’ in SQL Server 2008 Management Studio to find out how to change the configuration option that will allow you to make changes that require you to drop and recreate tables.
- Review the complete ALTER TABLE syntax in Books Online
Ken Simmons is a database administrator, developer, and Microsoft SQL Server MVP. He is the Author of Pro SQL Server 2008 Administration (Apress, 2009), Pro SQL Server 2008 Mirroring (Apress, 2009), and Pro SQL Server 2008 Policy-Based Management (Apress, 2010). He has been working in the IT industry since 2000 and currently holds certifications for MCP, MCAD, MCSD, MCDBA, and MCTS for SQL 2005.
