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.