Using Calculated Column In SQL Server


 
     Calculated columns can add great flexibility in database design .We can apply formula while creating tables to get computed values.below example help you to create computed column in sqlsever.

1. Create a temp table with calculated columns. Here column Total is the calculated one. It is populated sum of Mark1 and Mark2

CREATE TABLE #TempCalculatedTable(
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[Mark1] [int] NULL,
[Mark2] [int] NULL,
[Total] AS (ISNULL([Mark1],0)+ISNULL([Mark2],0)) /*Computed Column*/
)

2. Inserting data to temp table. Here we are inserting ID, Name, Mark1,Mark2 to temp table.

INSERT INTO #TempCalculatedTable
SELECT 1,'Jack',1000,2000
UNION ALL
SELECT 2,'Martin',2000,3000
UNION ALL
SELECT 3,'Stella',2000,3000
UNION ALL
SELECT 3,'Stella',NULL,3000
UNION ALL
SELECT 3,'Stella',NULL,NULL

3. Selecting data from temp Table. Here we can see calculated column Total populated automatically against each row.

SELECT *
FROM #TempCalculatedTable






2 comments:

  1. Calculated colum is new for me. Its very simple and very helpful . Expecting these type of topics that helping for all level of peoples.

    ReplyDelete