SQL SERVER – Using CASE Expression in Default Value

It is not possible to use CASE Expression in Default Values when you add a new column. I hope this answers the question. However, there is a workaround. Let us learn that today.

After reading the previous blog post SQL SERVER – Altering Column – From NULL to NOT NULL, I received a few emails and comments about whether the new column is populated based on the condition. Actually, it is not possible.

The workaround contains two steps.

Step 1: Add Column

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(100) NOT NULL DEFAULT ''

Step 2: Apply Condition Update

UPDATE TableName
SET ColumnName = 'FirstVal'
WHERE Col1 = 1;
UPDATE TableName
SET ColumnName = 'SecondVal'
WHERE Col1 = 2;
--... and so on...

Well, that’s it. Sometimes the question is complex but the answer is simple. I hope you enjoyed this simple blog post about case expression.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• 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

Reference: Pinal Dave (http://blog.SQLAuthority.com)