SQL SERVER COMPUTED COLUMNS


Computed Columns


 In SQL Server, computed columns refer to virtual columns that aren't physically stored in the table unless specified as persisted. These columns are calculated at runtime and are based on other columns within the same table. Their usage helps streamline intricate calculations, enhance data integrity, and facilitate data retrieval.

To Create a computed column within SQL Server, utilize the AS keyword alongside an expression defining the computation. Below is a fundamental syntax for crafting a computed column:


CREATE TABLE TableName (
Column1 datatype,
Column2 datatype,
ComputedColumn AS (Expression) [PERSISTED]
);


Here it is:
  • TableName: Name of your table
  • Column1 and Column2: Existing columns in the table
  • ComputedColumn: Name of the computed column
  • datatype: Data type of the respective columns
  • Expression: Computation expression
You can use various SQL functions, operators, and other columns in the computation expression. Here's an example of how to create a computed column that calculates the total price based on quantity and unit price:

CREATE TABLE Sales (
Quantity INT,
UnitPrice DECIMAL(10, 2),
TotalPrice AS (Quantity * UnitPrice)
);

In this example, the TotalPrice computed column is created based on the product of Quantity and UnitPrice.


Now Let's insert a record:

INSERT INTO Sales (Quantity, UnitPrice)
VALUES (5, 10.50);

 This INSERT statement will add a record to the 'Sales' table with a Quantity of 5 and a UnitPrice of 10.50. Since we have defined the 'TotalPrice' column as a computed column based on the product of 'Quantity' and 'UnitPrice', the value for the 'TotalPrice' column will be automatically calculated and stored in the table.


To view the result, you can use a simple 'SELECT' statement:



In this case, the TotalPrice column is automatically computed as 52.50, which is the product of the Quantity (5) and the UnitPrice (10.50).