(…with technical guidance from Avi Moskovitz…)

Recently, I needed to change the width of a column in the SQL Server portion of one of our Databases using MS Management Studio 2008.  And while this meant changing data type, you’d think it would be as easy as simply changing the properties within the table in the same manner that you can within Access 2010; normally, less than one minute to get ‘er done.  And in organizations where it is permissible to un-check the “Prevent saving changes that require table re-creation” on production systems, this might well be the case  (as in the graphic below).

The "Prevent saving changes that require table re-creation" option

But, in my situation, I had to find another route; this was going to require scripting…

1 – A Wild Goose Chase

A few quick searches of things like, “How do you change the width of a column in SQL”, and the results put a big grin across my face — I found reference to a MODIFY statement with an example of a column width change:

ALTER TABLE my_table MODIFY this_column VARCHAR2(50);

My problem was solved!  Or, so I thought.  After trying a few times and getting error messages, I almost started to suspect a conspiracy — or at least a concerted effort was in place to confuse and befuddle…

Luckily, Coretek is staffed with experts on a wide variety of software and hardware platforms.  I reached out to our resident SQL guru, Avi Moskovitz, who informed me the “solution” I found referencing the  MODIFY statement WAS accurate – if used in an Oracle environment – but MS SQL 2008 does not support it. 

2 – Drastic, Dangerous, but Legitimate

One possible option is to create a new column and delete the old column; something you should not be in a hurry to do if you have data in the column as you need to think about how the data will be repopulated within the column (will it affect links, relationships, etc., or will it be truncated or corrupted?).  I have found that creating a new column in the SQL backend (with the correct parameters) and then going to the front end (if you happen to have something like an Access front-end) to copy the information from the old column and pasting into the new column will work; but beware of deleting a key or a linked field. 

3 – More Drastic, Less Desirable

Another, more drastic and less desirable option is to re-create the entire table in the SQL backend with the correct parameters.  This is a multi-step process that, depending on the size of the table, can take 5 to 30 minutes (or more, if there are hiccups along the way).  In doing this, you are effectively manually re-creating what SQL does when changing a data type for a field.  Not necessarily recommended, but I am keeping these steps provided by Avi in case I ever need them:

1. Create a temporary table which will host your data (let’s call it MyTempTable).
2. Copy the data from the original table (MyFirstTable) to MyTempTable, making sure that you set Identity_Insert “ON” so that it keeps the Key Field intact when the data gets copied in.
3. Delete MyFirstTable. 
4. Recreate the original table (a duplicate if you will of MyFirstTable…MySecondTable)
5. Copy the data from the MyTempTable to the New MySecondTable making sure that you set Identity_Insert “ON” so that it keeps the Key Field intact when the data gets copied in.

4 – There’s a Right Way

But perhaps the best way of all, is to use a variant of the first thing mentioned above, correctly formatted for MS SQL 2008 with ALTER COLUMN instead of MODIFY, as follows:

 ALTER TABLE my_table ALTER COLUMN this_column NVARCHAR(50);

Execute this command into the query analyzer, and in an instant the problem is solved; the data type is changed (as in the graphic below)! 

SQL ALTER COLUMN Command - Successful!

Fortunately, the query analyzer will fail to execute if it detect data that does not fit the new type.  So what’s the lesson?  Internet searches are not a fool-proof way to explore scripting options; without the experience to understand the ramifications, the options can sometimes do as much harm as good…

Tags: access 2010, alter column, alter table, column width, database, Management Studio 2008, modify, MS, sql 2008, SQL server