How to Use Computed Column SQL Server Databases
What is Computed Column ?
If you have columns in your sql database tables such that their values depend on other columns, you may use computed columns.
Using computed columns or calculated columns will enable you save the calculation logic of such computed values in database.
And will save you from extra coding everytime you have to do when you require the computed item.
A computed column can be defined as an expression which can use other columns in the same table as well as using other table values via user-defined functions.
The expression of the computation can be directly a name of a noncomputed column (*), constants, functions including sql or user-defined functions or can contain all in the computed column definition.
It is important that the computed column expression can not be a subquery which means you can not use SELECT statements in the expression. Even if you are selecting a constant value like "SELECT 1" .
(*) As a note :
A computed column in a table is not allowed to be used in another computed-column definition in the same table.
You are reference a computed column in another table by using a user defined function in the expression of the calculated column.
Computed columns are actually not physically created on the related database table unless they are defined as "Persisted" computed columns.
You can think of computed columns as virtual columns. They are not physically stored in the related sql database table.
They are calculated every time they are referenced in a sql statement.
If you want to create an index on the computed column, then
If you require the computed column to be stored in the database instead of calculating everytime, you should define the computed column as persisted using the "PERSISTED" keyword in CREATE TABLE ... ADD column or ALTER TABLE ... ALTER COLUMN statements.
Their values are updated when any columns that are part of their calculation change.
By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise.
Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic.
Computed columns just like an ordinary table column can be used in a select list, can take place in WHERE clauses and in ORDER BY clauses.
But computed columns cannot be the directly updated or values can not be inserted into computed columns using INSERT or UPDATE statements.
Subqueries are not allowed in this context. Only scalar expressions are allowed.
A computed column is computed from an expression. But the expression cannot be a subquery.
If you need to execute SELECT statement to gather required data for the computed column value expression, then a user-defined function can be used.
You can build a user-defined function which can run SELECT statements.
But if you decide to create or alter your calculated column to be persisted, you may experience some other problems relating with determinism especially for XML data types and XQuery.
You may solve the problem for creating a persisted computed column by defining the user defined function (udf) which is used in the expression by using the "WITH SCHEMABINDING" with user sql function.
Cannot Create Persisted Computed Columns using User-Defined Functions with Select From Other Tables
You can not create calculated columns / computed columns as persisted if there is a user-defined function in the computed column expression and this function runs SELECT queries from database tables.
This is because if you are using a user defined sql function in the calculated column expression, to make the computed column persisted, udf must be deterministic.
For determinism for such a sql function, you must define the user defined t-sql function with "WITH SCHEMABINDING" .
In this case, schema binding will require the table names in two-part format.
And when you alter the sql function with tables names in two part format including the schema name and "WITH SCHEMABINDING" hint, this time the function will not be able for use in an expression of a persisted column.
Because then the column will be defined with user data access which is not suitable for a persisted computed column.
T-SQL Computed Column Sample Codes for SQL Server Tables
Here you can find samples where computed column properties are being illustrated with sample t-sql code.
CREATE TABLE Authors
(
AuthorId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(100),
LastName nvarchar(100),
FullName AS (FirstName + SPACE(1) + LastName) -- computed column
)
The above table sample has the FullName computed column defined as the concatenation of two other column values in the same table.
This is a simple expression sample. We define the computed column by "AS" clause and see that we do not define a column type since the type is defined implicitly by the calculation expression.
INSERT INTO Authors (FirstName, LastName) VALUES (N'Roger', N'Wolter')
INSERT INTO Authors (FirstName, LastName) VALUES (N'Dejan', N'Sarka')
SELECT * FROM Authors
Let's add an other computed column where a CASE expression is used in the definition of the calculated column value.
ALTER TABLE Authors ADD FullName2 AS (CASE WHEN AuthorId < 3 THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END)
Now insert some data into the sql table, and see the results.
INSERT INTO Authors (FirstName, LastName) VALUES (N'Itzik', N'Ben-Gan')
SELECT * FROM Authors
Let's now create a subsidiary SQL Server database table which we will use in our following samples.
Create Table DBParameters (
ParameterId int Identity(1,1) NOT NULL,
ParameterDescription nvarchar(1000),
ParameterValue nvarchar(1000)
)
GO
insert into DBParameters select N'First Name is before Last Name', N'1'
And now we will try to use a sub-select query in the computed column expression.
ALTER TABLE Authors ADD FullName3 AS (CASE WHEN EXISTS(SELECT * FROM DBParameters) THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END)
And the error that the SQL Engine will throw is:
Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Since subselects are not allowed, I will create a user-defined function to implement the same control.
CREATE FUNCTION GetBooleanParameterValue(
@ParameterId as int
) RETURNS bit
AS
BEGIN
DECLARE @result bit
SELECT @result = CAST(ParameterValue AS bit) FROM DBParameters WHERE ParameterId = @ParameterId
RETURN @result
END
GO
SELECT dbo.GetBooleanParameterValue(1)
Now, I'll try using the user-defined function instead of the sub-select query.
ALTER TABLE Authors ADD FullName3 AS (CASE WHEN dbo.GetBooleanParameterValue(1) = 1 THEN (FirstName + SPACE(1) + LastName) ELSE (LastName + SPACE(1) + FirstName) END)
UPDATE DBParameters SET ParameterValue = 1 WHERE ParameterId = 1
SELECT FirstName, LastName, FullName3 FROM Authors
UPDATE DBParameters SET ParameterValue = 0 WHERE ParameterId = 1
SELECT FirstName, LastName, FullName3 from Authors
The first select displays the firstname before the lastname in the fullname.
And after the parameter table is updated, the second select displays the lastnames before the last.
Let's create an other table which will help us in the next computed columns.
CREATE TABLE [Author Expertise]
(
Id int Identity(1,1) NOT NULL,
AuthorId int,
Expertise nvarchar(1000) NOT NULL
)
GO
INSERT INTO [Author Expertise] SELECT 1, N'T-SQL'
INSERT INTO [Author Expertise] SELECT 1, N'CLR'
INSERT INTO [Author Expertise] SELECT 2, N'CLR'
INSERT INTO [Author Expertise] SELECT 2, N'XML'
INSERT INTO [Author Expertise] SELECT 3, N'T-SQL'
INSERT INTO [Author Expertise] SELECT 3, N'CLR'
INSERT INTO [Author Expertise] SELECT 3, N'Service Broker'
GO
CREATE FUNCTION GetAuthorExpertiseCount(
@AuthorId as int
) RETURNS int
AS
BEGIN
DECLARE @result int
SELECT @result = COUNT(*) FROM [Author Expertise] WHERE AuthorId = @AuthorId
RETURN @result
END
GO
SELECT dbo.GetAuthorExpertiseCount(3)
ALTER TABLE Authors ADD Expertise_Topics_Count AS ( dbo.GetAuthorExpertiseCount(AuthorId) )
SELECT * FROM Authors
ALTER TABLE Authors ADD BooksInfo XML
GO
UPDATE Authors
SET BooksInfo = N'
<books>
<book title="Inside Microsoft� SQL Server� 2005: T-SQL Programming">
<technology>Microsoft SQL Server 2005</technology>
</book>
<book title="Microsoft� SQL Server� 2008 T-SQL Fundamentals">
<technology>Microsoft SQL Server 2008</technology>
</book>
</books>'
WHERE AuthorId = 3
GO
CREATE FUNCTION GetLastBookTitle (
@BooksInfo XML
) RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @Title nvarchar(1000)
select
@Title = i.value('@title', 'nvarchar(1000)')
from @BooksInfo.nodes('/books/book') n(i)
order by CONVERT(datetime, i.value('./publishdate[1]/.', 'nvarchar(1000)'), 103) desc
RETURN @Title
END
GO
ALTER TABLE Authors ADD LastBookTitle AS dbo.GetLastBookTitle(BooksInfo)
GO
SELECT AuthorId, Fullname3, BooksInfo, LastBookTitle FROM Authors
ALTER TABLE Authors ADD BirthDate datetime
GO
UPDATE Authors SET BirthDate = '19700101' WHERE AuthorId = 1
GO
ALTER TABLE Authors ADD Age AS DATEDIFF(yy, BirthDate, GETDATE())
Let's add an other age table which is depending on another calculated column.
ALTER TABLE Authors ADD Age2 AS DATEADD(yy, Age, BirthDate)
Unfortunately, a computed column can not be based upon another calculated field.
The SQL Server error message will be:
Msg 1759, Level 16, State 0, Line 1
Computed column 'Age' in table 'Authors' is not allowed to be used in another computed-column definition.
Also a user- defined function which is using the computed table column in its t-sql code cannot be used in another computed column expression.
CREATE FUNCTION TestUDF(
@Age int,
@BirthDate datetime
) RETURNS datetime
AS
BEGIN
RETURN DATEADD(yy, @Age, @BirthDate)
END
GO
ALTER TABLE Authors ADD Age2 AS dbo.TestUDF(Age, BirthDate)
The SQL Engine will throw the following error message in this ALTER TABLE command.
Msg 1759, Level 16, State 0, Line 1
Computed column 'Age' in table 'Authors' is not allowed to be used in another computed-column definition.
What about if we want to define the computed column as PERSISTED column in order to create indexes on these columns.
ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo.GetLastBookTitle(BooksInfo) Persisted
Msg 4936, Level 16, State 1, Line 1
Computed column 'LastBookTitlePersisted' in table 'Authors' cannot be persisted because the column is non-deterministic.
The reason while we can not define the LastBookTitlePersisted column as PERSISTED is the computed column is non-deterministic because of the user-defined function dbo.GetLastBookTitle() is an XML based function.
But the solution for this problem is simple for this udf.
If we alter the procedure by adding the "WITH SCHEMABINDING" hint, we can manage creating the related column persisted.
CREATE FUNCTION GetLastBookTitle2 (
@BooksInfo XML
) RETURNS nvarchar(1000)
WITH SCHEMABINDING -- The "WITH SCHEMABINDING" is needed if I want to the column a persisted computed column
AS
BEGIN
DECLARE @Title nvarchar(1000)
select
@Title = i.value('@title', 'nvarchar(1000)')
from @BooksInfo.nodes('/books/book') n(i)
order by CONVERT(datetime, i.value('./publishdate[1]/.', 'nvarchar(1000)'), 103) desc
RETURN @Title
END
Now we can successfully create the column as persisted.
ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo.GetLastBookTitle2(BooksInfo) Persisted