Skip Navigation LinksHome > View Post

Changing the size of a column in SQL Server Management Studio

I knew my SQL was getting rusty but I was sure I could adjust the size of an existing column on a table populated with data. However, today when I tried to increase a varchar column from 100 to 255 I got a nasty error:

image

As ever, when presented with an error like this the male part of my brain that ignores the details when receiving driving directions from a stranger kicks in. However, unlike most errors this one actually contained the exact details to remedy the situation. All you have to do is go to Tools > Options > Designers and uncheck the Prevent saving changes that require table re-creation.

image 

Game on. That is all.

Tags: SQL

 
Josh Post By Josh Twist
9:40 PM
24 Aug 2010

» Next Post: Learning MVC: Backwards compatible routes
« Previous Post: Feels like a new day – implementing the MetaWeblog API

Comments are closed for this post.

Posted by Josh @ 24 Aug 2010 9:42 PM
How much am I loving Windows Live Writer? Yes, I know I should be using nvarchar. It's an old database, what can I say?

Posted by valhallasw @ 25 Aug 2010 9:39 AM
Doesn't the table re-creation destroy all data?

Posted by Josh @ 25 Aug 2010 9:41 AM
I hope not. I just did it to live! On this blog! Not sure exactly what happends internally though (suspect data might be moved around temp tables or something). Anybody know?

Posted by Andy Waldman @ 08 Nov 2010 4:57 PM
When you use the Table Designer in SQL Management Studio, changes in the table will be made by:
1. Creating a new table with a temporary name (tmp_abc)
2. Moving all the data from your original table into your new table in a single statement
3. Drop the original table (abc)
4. Rename the new table to the original table name (tmp_abc -> abc)

So, to recap, no data will be destroyed, but if you are making this change on a large table your will need enough free space in your db to copy the whole table and manage that copy in the transaction log (which will need about 2 times as much space as the size of the table). Also, all indexes and constraints will be recreated on the new table afterward. This is all fine for smaller tables, but if this was a large table that could cause some issues.

For a larger table, you can use the below SQL. The type of change you are making to your existing table is what we call a metadata only change. This means that you are not altering the physical storage of the table, but just telling SQL server to now allow the varchar column to be larger. These types of changes will be instantaneous.

ALTER TABLE dbo.abc ALTER COLUMN col1 varchar(2000);


Hope that helps!

© 2005 - 2014 Josh Twist - All Rights Reserved.