Conditionally add a column with a default value to an existing table in SQL Server

Question

I want to add a new column in existing SQL table with default values depending upon some cases/conditions.

I want to know if this is possible. If yes how? if not why?

Alternative :

One option is to create the column and then update but approach is out of the question here.

Let’s consider I have the following table

╔════╦══════════════╗
║ ID ║      Age     ║ 
╠════╬══════════════║
║  1 ║    0.166667  ║   
║  2 ║     0.125    ║   
║  3 ║       13     ║   
║  4 ║       19     ║  
║  5 ║       45     ║  
║  6 ║       59     ║   
║  7 ║       60     ║  
║  8 ║       64     ║ 
╚════╩══════════════╝

Desired output is this :

╔════╦══════════════╦═══════════╗
║ ID ║      Age     ║ AgeGroup  ║
╠════╬══════════════╬═══════════╣
║  1 ║    0.166667  ║   NewBorn ║
║  2 ║     0.125    ║   NewBorn ║
║  3 ║       13     ║   Teen    ║
║  4 ║       19     ║   Teen    ║
║  5 ║       45     ║   Adult   ║
║  6 ║       59     ║   Adult   ║
║  7 ║       60     ║  Elder    ║
║  8 ║       64     ║  Elder    ║
╚════╩══════════════╩═══════════╝

I have studied this post but this is only for "Adding column default values"

EDIT : Here is the SQL script with schema and data for the above table for users who wants to test.

I don't understand what you are trying to do here. Do you want a new column on the table or not? What's the condition that the new column depends on? Normally you would put your conditions on a `IF` control flow statement. — EzLo, Aug 27 '19 at 07:39
What exactly do you mean by "default value?" Is it possible, for example, to later update the value from teen to adult without changing the age? — Salman A, Aug 27 '19 at 07:44
@SalmanA Yes I want it to change. Basically add a column with different values in every row depending upon a condition. I want to do it at the time adding a column and don't want to write an update query which does that. — Sheraz Ahmed, Aug 27 '19 at 07:48
There is an answer below that suggests using computed columns. But even better solution is to not use computed columns (in your example they simply add noise) and use a CASE statement in your reports/ui to convert age value to labels. Even better would be to store date of birth so that you don't have to increment the age every year. — Salman A, Aug 27 '19 at 07:52
Just to clarify the question. If ID 3’s age were changed to 45, should the `AgeGroup` change to match? If so, then you’re not talking about a __default__, but a __computed column__. You should _never_ have two dependent columns, but it’s OK to have a virtual computed column. — Manngo, Nov 11 '21 at 06:08

DarkRob · Accepted Answer · 2019-08-27T09:50:38.147

You may try this. There are basically 2 approach to achieve this.

Adding Column in table schema

As I understand you want to add a column in table schema as given link shared by you. You may use case statement to add column for calculated values.

EDIT : Correcting Syntax Error

alter table yourtable
    add AgeGroup as (case 
                        when Age < 2 then 'New Born'
                        when Age < 19 then 'Teen'
                        when Age < 45 then 'Young'
                        when Age > 60 then 'Adult'
                        else 'Invalid' 
                     end);

Or

Create view

You may create a view for same and use it wherever you needed.

Create View TableResult 
As
Select Id, Age, case Age 
                         WHEN Age < 2 then 'New Born'
                         WHEN Age < 19 then 'Teen'
                         WHEN Age < 45 then 'Young'
                         WHEN Age > 60 then 'Adult'
                         else 'Invalid' 
                     end as AgeGroup
End

Adding Column in table schema is the approach that I am after but it throws a syntax error — Sheraz Ahmed, Aug 27 '19 at 08:44
There is a Syntax error in your answer which I have corrected "Adding Column in table schema" And thank You it works — Sheraz Ahmed, Aug 27 '19 at 09:27

Zaynul Abadin Tuhin · Answer 2 · 2019-08-27T07:40:05.243

4

use case when

        select case when age>0 and age<13 then 'new born'
        when age>=13 and age<=19 then 'teen'
        .................
           ... end -- put here more condition as your need

edited Aug 27 '19 at 07:40

answered Aug 27 '19 at 07:14

Zaynul Abadin Tuhin

31,407
5
33
63

this is simply a select script with cases Please read the question again, thank you – Sheraz Ahmed Aug 27 '19 at 07:36
@SherazAhmed use case when in times of table creation – Zaynul Abadin Tuhin Aug 27 '19 at 07:39

score 4 · Answer 3 · answered Aug 27 '19 at 07:25

4

You can use a calculated Field.

Your table design will be like this:

 CREATE TABLE [dbo].[MyTbl](    
 [ID] [INT] IDENTITY(1,1) NOT NULL, 
 [Age] DECIMAL(10,3) NOT NULL,  
 [AgeGroup]  AS (CASE WHEN Age < 1 THEN 'Newborn' 
                      WHEN Age BETWEEN 1 AND 13 THEN 'Kid' 
                      WHEN Age BETWEEN 13 AND 60 THEN 'Adult' 
                      WHEN Age > 60 THEN 'Elder' END)  
 CONSTRAINT [PK_ID]
 PRIMARY KEY CLUSTERED  (   [ID] ASC ))

Your field will be filled automatically, you just have to add your age.

answered Aug 27 '19 at 07:25

Zeina

1,573
2
24
34

as I mentioned in the question "I want to add a new column in existing SQL table" – Sheraz Ahmed Aug 27 '19 at 08:57
This does not address the actual problem but Thank you for this. this increased my knowledge about computed columns in SQL https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017 – Sheraz Ahmed Aug 27 '19 at 09:18

score 1 · Answer 4 · answered Aug 27 '19 at 07:29

1

First, you will have to create the AgeGroup column to the table:

ALTER TABLE yourTable
ADD AgeGroup VARCHAR(10)

As for making default values based on set conditions:

UPDATE yourTable
SET AgeGroup = CASE WHEN yourTable.Age >= 0 AND yourTable.Age < 2 THEN 'NewBorn'
WHEN yourTable.Age >= 13 AND yourTable.Age <= 19 THEN 'Teen'
WHEN yourTable.Age >= 20 AND yourTable.Age <= 59 THEN 'Adult'
WHEN yourTable.Age >= 60 THEN 'Elder' END

answered Aug 27 '19 at 07:29

Max Voisard

1,685
1
8
18

Please read this in the question "One option is to create the column and then update but approach is out of the question here." – Sheraz Ahmed Aug 27 '19 at 07:34
SQL Scripts like these are the closest you can get to generate default values. You can't simply add numbers into your `AgeGroup` column with the SQL Server user interface and expect to be able to configure SQL Server to know how to categorize the age group based on the age. If you could, it would be hard to find documentation on that. I would stick to an SQL script to generate default values on preset conditions. – Max Voisard Aug 27 '19 at 07:44

Conditionally add a column with a default value to an existing table in SQL Server

4 Answers4