Need and advantages of using computed columns
It might happen that sometimes you write a stored procedure, triggers or function to compute a certain value from the value of some other column. For example, you might write a SP or function to derive the age of an individual based on his date of birth. Although nothing is wrong here but instead of using the triggers or stored procedure to see the result, wouldn’t it be better to see such computed result directly as one of your columns of select query. This is very demanding in the case where we need to get rid of the overhead of calculations while generating reports, since the use of triggers for synchronizing updated columns degrades the performance.
For such scenarios where calculated values are required or values needs to be derived from other columns, computed columns come to our rescue.
A computed column is computed from an expression that can also use another column or columns in the same table. Any combination of functions, variables, constants, non-computed column with operators in between may be used to create a computed column. Computed columns are useful when a calculation must be recomputed on the same data repeatedly in referencing queries.
To use computed columns, you first specify a database column as a “Computed column” by specifying a formula. So when your SQL/T-SQL statement has the computed column in the select list, the SQL engine will apply the formula (You defined earlier) to derive the value for this column.
Let us see an example on how to use computed columns.
First we will create a script for creating table with a computed column.
Let the table be employee with three columns EmpId, DateOfBirth and DORetirement.
Here "Date of Retirement" is a computed column and is calculated for each employee as (DOBirth + 60 years - 1 day). Instead of calculating the value of this column each time while generating report or by updating the column [DORetirement] through a trigger whenever [DateOfBirth] is updated, we have created [DORetirement] as a computed column as shown below:
Create a table with computed column
CREATE TABLE [dbo].[Employee]
(
[EmpId] [int] NULL,
[EmpName] [int] NULL,
[DOB] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOB])-(1)) PERSISTED
)
Computed column has been declared as persisted. This is very important for any computed column, because many features depend on this property. Only deterministic computed column can be declared as persisted.
Why to use persisted property?
If Persisted property is off then computed column will be just like a virtual column. Data for this column will not be stored on disk and values will be calculated every time this column is referenced in a query. If this property is on then data of computed column will be stored on disk and in this case, value of computed column will change only when the referenced column values are changed i.e. In our example, DORetirement column value will be updated only when DOB value is changed.
Persisted is also required to create an index on the computed column.
If you are not comfortable writing queries then you can also do this using SSMS User interface as shown below:
It is not necessary that the computed columns can be used only while creating tables. We can add a computed column to any existing table using the ALTER TABLE command or through SSMS User interface.
To see how it’s works, let’s add some test data
INSERT INTO Employee(empId,DOB)
SELECT 1 ,'1987-12-13' UNION ALL
SELECT 2 ,'1982-11-20' UNION ALL
SELECT 3 ,'1975-01-10' UNION ALL
SELECT 4 ,'1980-02-19' UNION ALL
SELECT 5 ,'1965-05-13'
Now when we fire our select query, then we can see our computed column result as shown below:
To confirm that the computed column is updated whenever its referenced column is updated, we will update [DOB] for [empId] 2 using the below query
UPDATE Employee
SET DOB = '1964-05-25'
WHERE EmpId = 2
Now when we run our select query, then we see query result as.
From above query result, we have verified that our computed column gets updated whenever its referenced column is updated.
Using a computed column in this way allow you to do the entire calculation in the back-end. But sometimes, this is very expensive especially in the case where the table contains large number of rows and there are many computed columns. The situation would be worse if the computed column is specified in the WHERE clause in a SELECT statement because in this case, the database engine has to calculate computed column’s value for each row in the table after which the filtering where condition would be applied. If you are aware of the indexing process then you will understand that this is a very inefficient process because it always requires a table or full clustered index scan.
To improve performance on computed columns, you need to create index on the computed columns. When an index is created on a computed column, SQL Server does not calculate the result on your select query; instead the result is calculated in advance after which an index is created on that result. Moreover, when the column values are updated (on which computed column value is calculated), the index values on computed column are also updated. So, when you fire your query, the database engine does not execute the computation formula for every row of your result set. Rather, the indexed values for the computed columns are returned. In this way, creating index on computed columns boost the performance of your query.
Note: If you want to create index on a computed column, make sure that the formula you write for your computed columns does not contain any nondeterministic function (For example, getdate() is a nondeterministic function and should not be used because each time you call this function, value returned will be different)
Computed columns can’t be used within a DEFAULT or FOREIGN KEY constraint. A calculated column can’t be explicitly updated or inserted into (since its value is always derived).
Computed columns can be used within indexes, but must meet certain requirements, such as being deterministic (always returning the same result for a given set of inputs) and precise (not containing float values).
Nullability
Nullability for a computed column value will be determined by the database engine. To avoid possible overflows or underflows, the result of a non-nullable referenced column would be NULL in certain conditions. If needed, you can provide your own alternate value for NULL using the ISNULL (check_expression, constant).
Some limitations and Cons
1) Persisted computed column cannot be created in SQL Server 2000.
2) You cannot reference columns from other tables for a computed column expression directly.
3) If you are combining operators of two different data types in your expression of computed columns then operator of lower precedence will be converted to that of higher precedence. If implicit conversion is not possible then error will be generated.
4) Expression of a computed column cannot have a subquery.
5) Computed columns can be used in SELECT, WHERE or ORDER BY clauses or as regular expressions , but to use a computed column as CHECK, FOREIGN KEY or NOT NULL constraints, you have to set it as Persisted.
6) To use a computed column as Primary or Unique Key constraint, it should be defined by a deterministic expression and data type of computed column expression should be indexable.
7) Although persisted computed column will reduce overhead for calculations at run time, it will consume more space on disk.
Note that the computed column does not take any additional space in the database unless the created computed column is marked as PERSISTED. On the other hand, when an index is created, it does not increase the data size of the table; rather, the index created just uses up the unused space in the table.
without creating a persisted column, we can still get a better performance if we create the index on the column which was computed.
Note: Images used on this website
are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon
Foundation or were found in google search under "Free to use and share". If any
of the images presented here violates copyright issues or infringes anyone copyright
or are not under "Fair use", then please bring it to our notice. Read
Disclaimer for more.
Share this to your friends. One of your friend is
waiting for your share.
Related Articles
Loop through all records in Sql server with while statement
Need and advantages of using computed columns
Apply Multiple Order by on a list using LINQ
Binding Database columns with apostrophe
Parse comma separated input string in SQL
Different string functions in SQL-Part 1
Testing performance issues with reflection
Capitalise first character of every word
Get list of all database objects with table name or column name
Commonly used string operation in SQL
Post Comment