SQL SERVER – Legal CASE Defaults and Row Dependent Values

A CASE Expression can appear in a default, but can’t reference another row column there. My earlier blanket prohibition was incorrect.

CREATE TABLE #DefaultExample
 (ID int, Col1 int,
  ConstantCase int DEFAULT (CASE WHEN 1=1 THEN 10 ELSE 20 END));
INSERT #DefaultExample(ID,Col1) VALUES (1,1),(2,2),(3,3);
ALTER TABLE #DefaultExample ADD ColumnName varchar(100) NOT NULL DEFAULT '';
UPDATE #DefaultExample SET ColumnName=CASE Col1
 WHEN 1 THEN 'FirstVal' WHEN 2 THEN 'SecondVal' ELSE '' END;
SELECT ID,Col1,ConstantCase,ColumnName FROM #DefaultExample ORDER BY ID;
DROP TABLE #DefaultExample;

The constant CASE expression is valid. A Col1-dependent rule needs an explicit UPDATE, INSERT expression or suitable computed column. Choose derived versus independently stored data deliberately.

I also used invalid ALTER COLUMN … DEFAULT syntax. Add a new column with ALTER TABLE … ADD. An existing-column default uses ADD CONSTRAINT … DEFAULT … FOR. The add-and-populate pattern must preserve those distinctions.

The empty-string fallback matches this demonstration only. Choose a meaningful production fallback and validate existing values. Coordinate writers so the migration does not leave incorrectly initialized rows.

Check the expression requirement before choosing a constraint. A default can’t read another column from the same row. CASE itself is permitted when its inputs meet default rules.

Related reading

• SQL in Sixty Seconds
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

CASE syntax is not the default limitation, it is the row-column reference that this constraint can’t use.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.