“Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show

If you’re old enough to remember the Tonight Show hosted by Johnny Carson, how can you not remember the iconic Stupid Pet Tricks skits, and the later tributes to those done by David Letterman?

My Stupid T-SQL Tricks are hopefully a bit more useful, but just as amusing as Carson’s Stupid Pet Tricks were! Hopefully everyone knows what a DEFAULT CONSTRAINT is for a column in a T-SQL table.  Let’s first create a table with such a column and populate it with some sample rows.

CREATE TABLE #SampleData
(
    MyPrimaryKey    VARCHAR(10) NOT NULL
    ,MyDataColumn   INT
    ,MyDataColumn2  INT 
    ,CONSTRAINT sd_pk PRIMARY KEY (MyPrimaryKey)
);
ALTER TABLE #SampleData
ADD CONSTRAINT sd_df1
DEFAULT (50) FOR MyDataColumn2;
INSERT INTO #SampleData (MyPrimaryKey, MyDataColumn)
SELECT 'Dwain.C', 41 UNION ALL SELECT 'Joe.C', 54
UNION ALL SELECT 'Chris.M', 44 UNION ALL SELECT 'Jeff.M', 33;
SELECT *
FROM #SampleData;

We can see from the following results that when the column is not specified on INSERT, the default value applies to each row created.

MyPrimaryKey  MyDataColumn  MyDataColumn2
Chris.M       44            50
Dwain.C       41            50
Jeff.M        33            50
Joe.C         54            50

Let’s change a couple of the rows to a different value.

UPDATE #SampleData
SET MyDataColumn2 = 52
WHERE MyPrimaryKey IN ('Dwain.C','Jeff.M');
SELECT *
FROM #SampleData;

So now we have this:

MyPrimaryKey  MyDataColumn  MyDataColumn2
Chris.M       44            50
Dwain.C       41            52
Jeff.M        33            52
Joe.C         54            50

Suppose we want to write a generalized query that returns all rows that are set to the current default value for the column that has a default constraint.  We know that column is MyDataColumn2.

DECLARE @MyColDefault INT;
BEGIN TRANSACTION T1;
UPDATE TOP (1) #SampleData
SET @MyColDefault = MyDataColumn2 = DEFAULT;
ROLLBACK TRANSACTION T1;
SELECT *
FROM #SampleData
WHERE MyDataColumn2 = @MyColDefault;

Returns us these results:

MyPrimaryKey  MyDataColumn  MyDataColumn2
Chris.M       44            50
Joe.C         54            50

Using the TRANSACTION/ROLLBACK, we’ve pretended to set one row back to its DEFAULT value, while saving that DEFAULT value into a local variable which we can then use in the subsequent, filtered SELECT. Using the TOP (1) option on the UPDATE ensures that we’ve only touched a single row to get the DEFAULT value that applies to all rows.

You’ll find that your code is now impervious to some pesky DBA changing the default constraint value for that column.

ALTER TABLE #SampleData
DROP CONSTRAINT sd_df1;
ALTER TABLE #SampleData
ADD CONSTRAINT sd_df1
DEFAULT (52) FOR MyDataColumn2;
DECLARE @MyColDefault INT;
BEGIN TRANSACTION T1;
UPDATE TOP (1) #SampleData
SET @MyColDefault = MyDataColumn2 = DEFAULT;
ROLLBACK TRANSACTION T1;
SELECT *
FROM #SampleData
WHERE MyDataColumn2 = @MyColDefault;

So now we get different results:

MyPrimaryKey  MyDataColumn  MyDataColumn2
Dwain.C       41            52
Jeff.M        33            52

What we learned from these examples:

• How to create a named DEFAULT constraint for a column.
• How to change that constraint, by first dropping it and then creating it again.
• How to write a query to return all rows where the value of a column is set to its current default.

Now how is that for a stupid, T-SQL trick?

Follow me on Twitter: @DwainCSQL

© Copyright Dwain Camps 19 Mar 2015.  All rights reserved.