Computed column in SQL Server

By
Computed Column in SQL Server
Computed column are derived column of table and its depends on other existing column of that table. We can create computed column by two way
1. When we create the table
2. By using the Alter statement
I show you the example later on this article.Now lets talk about the datatype of computed column.
Datatype of computed column depends on the outcome of computed column.It may be same as depending column or it could be different.

Computed Column SQL Server


--Create a table and add a computed column
Create table tblOrder
(
orderId bigint,
quantity int,
price decimal(18,2),
totalprice as quantity * price
)
In above totalprice is computed column and as you can see i did not assign the datatype.Now insert some data in above table
INSERT INTO tblOrder(orderId,price,quantity)
SELECT 1,10,12.5
UNION ALL
SELECT 2,5,130.35
UNION ALL
SELECT 3,100,50.5
UNION ALL
SELECT 4,54,80

In computed column if we try to insert the data then its give error

INSERT INTO tblOrder(orderId,price,quantity,totalprice)
SELECT 1,10,12.5,100
if we trying the execute above query then its give error and error is like 
Msg 271, Level 16, State 1, Line 2
The column "totalprice" cannot be modified because it is either a computed column or is the result of a UNION operator.

Now if we execute the select query then output is like below
SELECT * FROM tblOrder

Now lets talk about the datatype of computed column.
DECLARE @tblComputed TABLE(id int, computed_id AS id, computed_date AS dateadd(day,id,getdate()))
INSERT INTO @tblComputed(id)
SELECT 9

SELECT * FROM @tblComputed


As you can see the datatype of computed column is different on different scenario .For example in case of computed_id its int but for computaed_date its like datetime.

Now we discuss how to create computed column with alter command
ALTER TABLE tblOrder add totalprice as quantity * price

Type Of Computed Column
There are two type of computed column
1. Persisted
2.Non-Persisted
By default computed column is non persisted.

Persisted Computed Column
Persisted computed columns are run whenever data is inserted or is updated in a table. A persisted computed field occupies memory for the data. Comparing to a persisted computed column is faster than a non-persisted computed column.
Create table tblOrder
(
orderId bigint,
quantity int,
price decimal(18,2),
totalprice as quantity * price persisted
)

Non-Persisted Computed Column 
Non-Persisted computed columns are run whenever data is selected from a table. A Non-Persisted computed field does not occupy memory for the data, because it is executed when the data is selected. Comparions to non-persisted computed columns is slower than persisted computed columns.By default computed column is non persisted means if we don't write keyword persisted then computed column treat as a non-persisted . 


Limitation

  • You cannot drop a dependent column whenever a computed column exists in a table.
  • You cannot do insert or update operations on a computed column.
  • You cannot change an existing computed column definition using an alter statement

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete