{"id":1878,"date":"2019-04-28T23:21:08","date_gmt":"2019-04-28T16:21:08","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1878"},"modified":"2020-04-11T20:12:14","modified_gmt":"2020-04-11T13:12:14","slug":"sql-server-computed-columns","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-computed-columns\/","title":{"rendered":"SQL Server Computed Columns"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server computed columns to reuse the calculation logic in multiple queries.<\/p>\n\n\n\n

Introduction to SQL Server computed columns #<\/a><\/h2>\n\n\n\n

Let’s create a new table<\/a> named persons<\/code> for the demonstrations:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> persons\n(\n    person_id  INT<\/span> PRIMARY KEY<\/span> IDENTITY<\/span>, \n    first_name NVARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>, \n    last_name  NVARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>, \n    dob        DATE<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> \n    persons(first_name, last_name, dob)\nVALUES<\/span>\n    ('John'<\/span>,'Doe'<\/span>,'1990-05-01'<\/span>),\n    ('Jane'<\/span>,'Doe'<\/span>,'1995-03-01'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n    person_id,\n    first_name + ' '<\/span> + last_name AS<\/span> full_name,\n    dob\nFROM<\/span>\n    persons\nORDER<\/span> BY<\/span>\n    full_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Adding the full_name<\/code> expression first_name + ' ' + last_name<\/code> in every query is not convenient.<\/p>\n\n\n\n
Fortunately, SQL Server provides us with a feature called computed columns that allows you to add a new column to a table with the value derived from the values of other columns in the same<\/em> table.<\/p>\n\n\n\n
For example, you can add the full_name<\/code> column to the persons<\/code> table by using the ALTER TABLE ADD<\/code> column<\/a> as follows:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> persons\nADD<\/span> full_name AS<\/span> (first_name + ' '<\/span> + last_name);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Every time you query data from the persons<\/code> table, SQL Server computes the value for the full_name<\/code> column based on the expression first_name + ' ' + last_name<\/code> and returns the result.<\/p>\n\n\n\n
Here is the new query, which is more compact:<\/p>\n\n\n
SELECT<\/span> \n    person_id, \n    full_name, \n    dob\nFROM<\/span> \n    persons\nORDER<\/span> BY<\/span> \n    full_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you examine the persons<\/code> table, you can see the new full_name<\/code> column appears in the column list:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Persisted computed columns #<\/a><\/h2>\n\n\n\n
Computed columns can be persisted. It means that SQL Server physically stores the data of the computed columns on disk.<\/p>\n\n\n\n
When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically. When you query the data from the persisted computed columns, SQL Server just needs to retrieve data without doing any calculation. This avoids calculation overhead with the cost of extra storage.<\/p>\n\n\n\n
Consider the following example.<\/p>\n\n\n\n
First, drop the full_name<\/code> column of the persons<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> persons\nDROP<\/span> COLUMN<\/span> full_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, add the new full_name<\/code> column to the persons<\/code> table with the PERSISTED<\/code> property:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> persons\nADD<\/span> full_name AS<\/span> (first_name + ' '<\/span> + last_name) PERSISTED;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that a computed column is persisted only if its expression is deterministic. It means that for a set of inputs, the expression always returns the same result.<\/p>\n\n\n\n
This formula returns the age in years based on the date of birth and today:<\/p>\n\n\n
(CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))\/10000\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
We can use this expression for defining the age in year computed column.<\/p>\n\n\n\n
The following statement attempts to define the age_in_year<\/code>computed column as a persisted computed column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> persons\nADD<\/span> age_in_years \n    AS<\/span> (CONVERT<\/span>(INT<\/span>,CONVERT<\/span>(CHAR<\/span>(8<\/span>),GETDATE<\/span>(),112<\/span>))-CONVERT<\/span>(CHAR<\/span>(8<\/span>),dob,112<\/span>))\/10000<\/span> \nPERSISTED;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL server issues the following error:<\/p>\n\n\n
Computed column 'age_in_years' in table 'persons' cannot be persisted because the column is non-deterministic.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you remove the PERSISTED<\/code> property, it should work:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> persons\nADD<\/span> age_in_years \n    AS<\/span> (CONVERT<\/span>(INT<\/span>,CONVERT<\/span>(CHAR<\/span>(8<\/span>),GETDATE<\/span>(),112<\/span>))-CONVERT<\/span>(CHAR<\/span>(8<\/span>),dob,112<\/span>))\/10000<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, you can query the age in years of people in the persons<\/code> table as follows:<\/p>\n\n\n
SELECT<\/span> \n    person_id, \n    full_name, \n    age_in_years\nFROM<\/span> \n    persons\nORDER<\/span> BY<\/span> \n    age_in_years DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The syntax for adding computed columns to a table #<\/a><\/h2>\n\n\n\n
To add a new computed column to an existing table, you use the following syntax:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nADD<\/span> column_name AS<\/span> expression [PERSISTED];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
For example, the expression first_name + ' ' + last_name<\/code> is deterministic. However, the GETDATE()<\/a><\/code> function is a non-deterministic function because it returns a different value on a different day.<\/p>\n\n\n\n
To query the full names of people in the persons<\/code> table, you normally use the CONCAT()<\/a><\/code> function or the +<\/code> operator as follows:<\/p>\n\n\n
And insert<\/a> two rows into the the persons<\/code> table:<\/p>\n\n\n