keyword DEFAULT

4

August 20, 2012 by Kenneth Fisher

In 12 years of working with SQL it was only last year that I came across the keyword DEFAULT and even then I didn’t really use it much. However just the other day a developer came to my desk with a problem, and of course DEFAULT was the answer. I guess I wouldn’t be bringing it up otherwise would I?

So here is the scenario. The developer was using an ETL tool to import data into a new table. Note: For the sake of the examples I’m just going to use literals for my inserts and updates.

Here is the table structure.

CREATE TABLE TableWithDefaults (
Id INT NOT NULL IDENTITY(1,1)
,FirstName varchar(50) ,LastName varchar(50)
,Address1 varchar(255) ,Address2 varchar(255)
,City varchar(50) ,State char(2) ,PostalCode varchar(10)
,Question1 varchar(50) ,Answer1 varchar(50)
,Question2 varchar(50) ,Answer2 varchar(50)
,Question3 varchar(50) ,Answer3 varchar(50)
,Question4 varchar(50) ,Answer4 varchar(50)
,Question5 varchar(50) ,Answer5 varchar(50)
,CreateDate datetime CONSTRAINT dfCreateDate DEFAULT GETDATE()
,CreatedBy varchar(20) CONSTRAINT dfCreatedBy DEFAULT SUSER_SNAME()
,LastUpdateDate datetime CONSTRAINT dfLastUpdateDate DEFAULT GETDATE()
,LastUpdatedBy varchar(20) CONSTRAINT dfLastUpdatedBy DEFAULT SUSER_SNAME()
)

Here is the initial insert statement she came to me with:

INSERT INTO TableWithDefaults
VALUES ('John','Smith','123 Street Dr','Apt 456','Cityville','TX','12345-6789'
,'Who?','Me','What?','This','When?','Today'
,'Where?','Here','Why?','Because')

Which gives the error:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

The error, of course, is because she hadn’t listed values for the last 4 columns in the table. I should probably point out that when she requested the table the she hadn’t requested those columns. They are part of a coding standard that our DBAs enforce.

Now the developer could have managed with this statement:

INSERT INTO TableWithDefaults
VALUES ('John','Smith','123 Street Dr','Apt 456','Cityville','TX','12345-6789'
,'Who?','Me','What?','This','When?','Today'
,'Where?','Here','Why?','Because'
,GETDATE(),SUSER_SNAME(),GETDATE(),SUSER_SNAME())

But that rather defies the point of having default constraints. For example if this code is used and the defaults need to be changed then all of the code like this will need to be changed, rather than just having to change the default constraints themselves.

Or they could have used this statement:

INSERT INTO TableWithDefaults
(FirstName, LastName, Address1, Address2, City, State, PostalCode
,Question1, Answer1, Question2, Answer2, Question3, Answer3
,Question4, Answer4, Question5, Answer5)
VALUES ('John','Smith','123 Street Dr','Apt 456','Cityville','TX','12345-6789'
,'Who?','Me','What?','This','When?','Today'
,'Where?','Here','Why?','Because')

But while this is safer code (no problems if columns are added to the table) it would be unwieldy if there were 20, 30, 100 etc columns. So what I ended up giving her was:

INSERT INTO TableWithDefaults
VALUES ('John','Smith','123 Street Dr','Apt 456','Cityville','TX','12345-6789'
,'Who?','Me','What?','This','When?','Today'
,'Where?','Here','Why?','Because'
,DEFAULT,DEFAULT,DEFAULT,DEFAULT)

The DEFAULT keyword causes the default value (from the constraint) to be inserted into the column. After the developer had left happy I started thinking, hey, this is cool, I wonder if it can also be used on updates. And as it happens it can:

UPDATE TableWithDefaults
SET FirstName = 'John'
, LastUpdateDate = DEFAULT
, LastUpdatedBy = DEFAULT
WHERE Id = 1

Of course it was only while writing this post did I remember that we also use update triggers on tables with the LastupDate columns that use the DEFAULT keyword.

CREATE TRIGGER tr_TableWithDefaults_LastUpdate
ON TableWithDefaults
FOR UPDATE
AS

IF UPDATE(LastUpdateDate) OR UPDATE(LastUpdatedBy)
RETURN

UPDATE TableWithDefaults SET
LastUpdateDate = DEFAULT
, LastUpdatedBy = DEFAULT
WHERE Id IN (SELECT Id FROM Inserted)
GO

It turns out that the DEFAULT keyword while something that isn’t really devastating can be quite useful when you need it.

About these ads

4 thoughts on “keyword DEFAULT

  1. Lee says:

    Nice. Thanks for sharing.

  2. Jacque Reed says:

    An alternative would have been to create an insert sproc for the table which used default values. The nice thing about this is that you don’t have to, but you can, put in values for the default items in a sproc.

    • True, and I’ve worked with systems where the standard was to have INSERT, UPDATE and DELETE stored procedures for every table. We then only granted access to the stored procedures and not directly to the tables.

  3. […] while back I talked about the DEFAULT keyword and using it to tell SQL to use the default value without having to specify an actual value. Well […]

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

Follow

Get every new post delivered to your Inbox.

Join 163 other followers

%d bloggers like this: