Using Computed Columns in SQL Server

Using Computed Columns in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
When it comes to database design in SQL Server, it is always recommended to create the database as flexible as possible. SQL Server provides several features for developers in order to be able to do so.

An example of such a feature are the “computed” and “persisted” columns.
As explained on MSDN BOL, computed columns’ values are computed by expressions that can use other columns in the same table.

Also, when using the PERSISTED keyword with a COMPUTED column, the data of the computed column is physically stored in the table.

Let’s see some code and try out this great feature!

First let’s create two tables representing product information:

–Note the column with name “totalPrice”: It is a computed column which computes
–the total price (price + VAT)

CREATE TABLE Product1
(

id int,
code varchar(50),
price float,

vatPerc float,
totalPrice as (price+(price*(vatPerc/100)))

)

GO

–Note again the column with name “totalPrice”: It is the same computed column as in table
–Product 1 with the difference that it uses the PERSISTED keyword.

CREATE TABLE Product2
(

id int,
code varchar(50),

price float,

vatPerc float,
totalPrice as (price+(price*(vatPerc/100))) PERSISTED

)

GO

———— Testing Table Product1 ————–
INSERT INTO Product1 (id,code,price,vatPerc)
SELECT 1, ‘PRODUCT01’,100,15
UNION ALL

SELECT 2, ‘PRODUCT02’,200,15

GO

* I know, I could use a Table Value Constructor (Row Constructors) but I would prefer that my examples to be compatible with all versions of SQL Server as Table Value Constructors is one of the new programmability features in SQL Server 2008 🙂

Note that in the above INSERT statement I am not inserting any values for the “totalPrice” column. Though, when taking a look at the generated results, the totalPrice values are calculated on the fly!

SELECT *
FROM Product1

GO

Results
——-

Similarly, testing table Product2 will work the same way with the only difference that this time,
the calculated values for the “totalPrice” column will be physically stored in the table:

———— Testing Table Product2 ————–
INSERT INTO Product2 (id,code,price,vatPerc)
SELECT 1, ‘PRODUCT03’,300,15
UNION ALL

SELECT 2, ‘PRODUCT04’,400,15

GO


SELECT *
FROM Product2

GO

Results
——-

* Note: By the time you are using a Computed column you cannot explicitly insert data into it. The data is computed based on the computed column’s definition (expression).


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.