A friend of mine sent me an email the other day asking about default values in SQL Server. I realized that I’ve had to think about this a few times over the years and I’ve been asked about it more than once, too.
Setup
We need a table first, right? We’ll also want a few sample rows in there.
CREATE TABLE Employees (
emp_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
emp_name varchar(50) NULL);
GO
INSERT INTO Employees (emp_name) VALUES ('a');
INSERT INTO Employees (emp_name) VALUES ('b');
GO
SELECT * FROM Employees;
/*
emp_id emp_name
----------- --------------------------------------------------
1 a
2 b
*/
How Defaults Work
So far we just have two rows in our two column table. It’s pretty boring. Let’s add a default value:
ALTER TABLE Employees
ADD last_modified DATETIME NULL CONSTRAINT Employees_last_modified
DEFAULT CURRENT_TIMESTAMP;
GO
We might as well add some new rows while we’re having fun with our employees, right?
INSERT INTO Employees (emp_name) VALUES ('c');
INSERT INTO Employees (emp_name) VALUES ('d');
What’s it look like now?
SELECT * FROM Employees;
/*
emp_id emp_name last_modified
----------- -------------------------------------------------- -----------------------
1 a NULL
2 b NULL
3 c 2010-12-06 18:21:37.787
4 d 2010-12-06 18:21:37.787
*/
Hold up. Employees 1 and 2 don’t have a last_modified value. Why not? Well, that’s because we’ve told SQL Server that our last_modified column can allow NULLs. They’re allowable in our table. If we wanted to automatically provide a default value when we added the constraint, we could do so by specifying the datatype as DATETIME NOT NULL. A best practice would be to add the column as a NULLable data type, add a value for all NULL rows, and set the column to NOT NULL.
If we do want to update a NULLable column and set it to the default value, we just issue an update using the DEFAULT keyword for the value. If that makes no sense, perhaps this example will help:
UPDATE Employees
SET last_modified = DEFAULT
WHERE last_modified IS NULL ;
What About Updates?
UPDATE Employees
SET emp_name = 'zzz'
WHERE emp_id = 3;
SELECT *
FROM Employees
WHERE emp_id = 3;
/*
emp_id emp_name last_modified
----------- -------------------------------------------------- -----------------------
3 zzz 2010-12-06 18:21:37.787
*/
As you can see, when we update employee 3, it doesn’t change the value of last_modified. That’s because the default value is only set on insert. We could specify DEFAULT in our UPDATE statement, but then we’d need to specify that every time we update the table. What can we do?
The Answer is Triggers
That’s right: triggers. If we want to track the modification timestamp of an object in the database, we need to use a trigger to keep things updated:
CREATE TRIGGER TR_Employees$AfterUpdate ON dbo.Employees
AFTER UPDATE
AS
BEGIN
UPDATE e
SET e.last_modified = CURRENT_TIMESTAMP
FROM dbo.Employees e
JOIN inserted i ON e.emp_id = i.emp_id;
END
GO
UPDATE Employees
SET emp_name = 'asdf'
WHERE emp_id = 1;
SELECT * FROM Employees;
/*
emp_id emp_name last_modified
----------- -------------------------------------------------- -----------------------
1 asdf 2010-12-06 18:34:04.340
2 b NULL
3 zzz 2010-12-06 18:21:37.787
4 d 2010-12-06 18:21:37.787
*/
And that, my friends, is how we keep a modification timestamp up to date.
