Search Here

Wednesday, April 15, 2009

Computed Column in Sql Server

In this short article, we will discuss how can we alter a computed column:

If you have columns in your sql database tables such that their values depend on other columns, you may use computed columns. Using computed columns or calculated columns will enable you to save the calculation logic of such computed values in database and will save you from extra coding everytime you have to do when you require the computed item.

A computed column can be defined as an expression which can use other columns in the same table as well as using other table values via user-defined functions.

Suppose we have the table schema as:

Create Table Example

(

Col1 Decimal(10, 2),

Col2 Decimal(10, 2),

Col3 Decimal(10, 2),

Col4 Decimal(10, 2),

Col5 Decimal(10, 2),

Col6 Decimal(10, 2),

Col7 Decimal(10, 2),

ComputedCol1 As (Col1 + Col2 - Col3),

ComputedCol2 As (Col4 + Col5 - Col6)

)


Now if you want to alter the computed column (say, ComputedCol1), you can not do it direclty by using ALTER command. Instead, you have to drop the column first and then add the column again with new computed value, as:

--Drop the column First

Alter Table Example

Drop Column ComputedCol2

--Now add the column again with new computed value

Alter Table Example

Add [ComputedCol2] As (Col5 + Col6 - Col7) 

You can add a computed column where a CASE expression is used in the definition of the computed column value as,

Alter Table Example

Add [ComputedCol3] As (Case When Col7 = 0 Then (Col1 + Col2 - Col3) Else (Col4 + Col5 - Col6) End) 

However, you can not use a computed column as a condition expression in the definition of other computed column as,

Alter Table Example

Add [ComputedCol4] As (Case When ComputedCol1 = 0 Then 0 Else (Col5 + Col6 - Col7) End) 

/*

--Sql server will throw the following error:

Msg 1759, Level 16, State 0, Line 1

Computed column 'ComputedCol1' in table 'Example' is not allowed to be used in another computed-column definition.

*/

So to resolve this use the computed column's calculated values as condition expression as,

Alter Table Example

Add [ComputedCol4] As (Case When (Col1 + Col2 - Col3) = 0 Then 0 Else (Col5 + Col6 - Col7) End) 

Regards
Rohit

No comments:

Post a Comment