Adding a New Default to a Column

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

This is a fairly simple process, but I bet more than a few people don’t know how to do it. I had to double check some syntax the other day, and I thought this is a perfect SQLNewBlogger post, based on something I had to (re)learn.

If I have a table, there are existing columns and I want to add a constraint to one, I need to alter it. To do that, I’ll use the ALTER TABLE x ADD CONSTRAINT syntax.

One of the common use cases is to add a default date to a date column. For example, I have a Blogs table and want to ensure the CreatedDate column is always populated. I’d do that with

ALTER TABLE dbo.Blogs
ADD CONSTRAINT df_SysUTCDate DEFAULT SYSDATETIME() FOR createdate;

In this case, I use ADD CONSTRAINT and then name the constraint. Using specific names is always good since this means I can be sure that I have matches between development, QA, and production.

I then use the DEFAULT keyword and follow this with a function name. I then use the FOR and the column name. This means I’ve added a default constraint to the CreateDate column and if no value is included in an insert statement, sysdatetime() used.

This is a basic idea, but one that few developers think about or include in their design. Learn to use defaults and add them when you start building or adding columns.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.