{"id":7610,"date":"2020-01-30T17:30:21","date_gmt":"2020-01-31T00:30:21","guid":{"rendered":"https:\/\/database.guide\/?p=7610"},"modified":"2020-02-14T16:35:19","modified_gmt":"2020-02-14T23:35:19","slug":"create-a-computed-column-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/create-a-computed-column-in-sql-server-t-sql\/","title":{"rendered":"Create a Computed Column in SQL Server using T-SQL"},"content":{"rendered":"

This article demonstrates how to use T-SQL<\/a> to create a computed column in SQL Server<\/a>.<\/p>\n

A computed column<\/dfn><\/a> is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED<\/code>.<\/p>\n

<\/p>\n

Example 1 – Create a Table with a Computed Column<\/h2>\n

Here’s an example of creating a table with a computed column, inserting data, then selecting the table’s contents.<\/p>\n

\r\nCREATE TABLE Products\r\n  (\r\n    ProductID int IDENTITY (1,1) NOT NULL,\r\n    ProductName varchar(255), \r\n    Quantity smallint,\r\n    Price money,\r\n    TotalValue AS Quantity * Price\r\n  );\r\n\r\nINSERT INTO Products (ProductName, Quantity, Price)\r\nVALUES \r\n  ('Hammer', 5, 10),\r\n  ('Corkscrew', 2, 7.50),\r\n  ('Kettle', 3, 25.15);\r\n\r\nSELECT *\r\nFROM Products;\r\n<\/pre>\n
Result:<\/p>\n
\r\n+-------------+---------------+------------+---------+--------------+\r\n| ProductID   | ProductName   | Quantity   | Price   | TotalValue   |\r\n|-------------+---------------+------------+---------+--------------|\r\n| 1           | Hammer        | 5          | 10.0000 | 50.0000      |\r\n| 2           | Corkscrew     | 2          | 7.5000  | 15.0000      |\r\n| 3           | Kettle        | 3          | 25.1500 | 75.4500      |\r\n+-------------+---------------+------------+---------+--------------+\r\n<\/pre>\n
In this case, the last column is a computed column. It multiplies the quantity column by the price column. This enables us to get a total value of the product currently in stock.<\/p>\n
Example 2 – Create a Persisted Computed Column<\/h2>\n
You can create a persisted computed column by adding the PERSISTED<\/code> argument. This will result in the computed value being physically stored in the table. If it’s not persisted, then the value is calculated each time you read the computed column.<\/p>\n
Here’s the same code example as the previous one, except this time I create a persisted computed column:<\/p>\n
\r\nCREATE TABLE Products\r\n  (\r\n    ProductID int IDENTITY (1,1) NOT NULL,\r\n    ProductName varchar(255), \r\n    Quantity smallint,\r\n    Price money,\r\n    TotalValue AS Quantity * Price PERSISTED\r\n  );\r\n<\/pre>\n
The only difference is the PERSISTED<\/code> argument.<\/p>\n
Example 3 – Update a Value<\/h2>\n
Now if a value is updated in the Quantity<\/code> or Price<\/code> columns, this will affect the total value returned by the computed column. This will occur whether the column is persisted or not.<\/p>\n
If somebody buys a hammer for example, this will affect the total value returned by the computed column:<\/p>\n
\r\nUPDATE Products\r\nSET Quantity = 4\r\nWHERE ProductId = 1;\r\n\r\nSELECT *\r\nFROM Products;\r\n<\/pre>\n
Result:<\/p>\n
\r\n+-------------+---------------+------------+---------+--------------+\r\n| ProductID   | ProductName   | Quantity   | Price   | TotalValue   |\r\n|-------------+---------------+------------+---------+--------------|\r\n| 1           | Hammer        | 4          | 10.0000 | 40.0000      |\r\n| 2           | Corkscrew     | 2          | 7.5000  | 15.0000      |\r\n| 3           | Kettle        | 3          | 25.1500 | 75.4500      |\r\n+-------------+---------------+------------+---------+--------------+\r\n<\/pre>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
This article demonstrates how to use T-SQL to create a computed column in SQL Server. A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[97,10,77,61],"class_list":["post-7610","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-computed-columns","tag-how-to","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7610","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=7610"}],"version-history":[{"count":9,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7610\/revisions"}],"predecessor-version":[{"id":39422,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7610\/revisions\/39422"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=7610"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=7610"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=7610"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}