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.

Answer 1

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