Take caution when altering a column using TSQL

1

February 17, 2016 by Kenneth Fisher

When developing in SQL Server you are eventually going to have to modify a column or two (at least). And if you use T-SQL to make the modifications (and I would recommend it most of the time) there is something you need to watch out for.

First remember that when you define a column there are three parts. Datatype, precision and nullability. (And sometimes identity but we are going to ignore that for these purposes.)

CREATE TABLE AlterColumns (
	Column1 datetime2 NULL,
	Column2 varchar(50) NOT NULL,
	Column3 int
	);

You’ll notice that in only one case did I mention all three things, in the other cases I accepted various defaults.

  • Column1 – datetime2 is the datatype, I accepted the default precision (7 in this case) and specified that this column is nullable.
  • Column2 – varchar is the datatype, 50 is the precision (There is a default precision for string datatypes. Don’t use it.) and the column is not nullable.
  • Column3 – int is the datatype, int has a fixed precision (you can’t change or even specify it) and I accepted the default of a nullable column.

 
So here is the thing. When you change one you change them all. That means if you don’t specify a precision when you can then you get the default. That’s not exactly a common problem though. Usually what you are changing is the precision (or possibly the datatype). What is a common mistake is not specifying the nullability.

ALTER TABLE AlterColumns ALTER COLUMN Column2 varchar(255)

You’ll see statements like this all the time. The problem is that it has an unexpected consequence. You just changed the column to the default of nullable, but it was non-nullable. Hopefully your code is careful about inserts/updates. It won’t ever try to put a NULL into the column so it isn’t likely to break anything. It’s still something you want to watch out for though. You made that column non-nullable for a reason. It should stay that way.

One thought on “Take caution when altering a column using TSQL

  1. […] Kenneth Fisher points out that there are defaults when altering columns: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: