SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.


Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...