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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
