What are computed columns, how to create a computed column?

Problem: What are computed columns, how to create a computed column?

Solution: Specify Computed Columns in a Table

Computed Columns are generally columns which are calculated based on a conditional logic or operator which will be used for addition, subtraction, multiplication and division. We can specify a computed column in a table creation or alter statement. We cannot explicitly insert into computed columns.

In some cases, these computed columns come to a great use where the default values will be calculated based on rows inserted or updated.

Syntax:

column_name AS computed_column_expression 

[ PERSISTED [ NOT NULL ] ] 

[  

    [ CONSTRAINT constraint_name ] 

    { PRIMARY KEY | UNIQUE } 

        [ CLUSTERED | NONCLUSTERED ] 

        [ WITH FILLFACTOR = fillfactor ] 

        [ WITH ( <index_option> [, ...n ] ) ] 

        [ ON { partition_scheme_name ( partition_column_name ) | filegroup  

            | "default" } ] 

    | [ FOREIGN KEY ]  

        REFERENCES ref_table [ ( ref_column ) ]  

        [ ON DELETE { NO ACTION | CASCADE } ]  

        [ ON UPDATE { NO ACTION } ]  

        [ NOT FOR REPLICATION ]  

    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 

] 

PERSISTED key word must be specified when you want to specify partition on that column.

Example:

In this example, I would like to create a computed column which will be calculated using input values.

I have unit price column where each item cost is specified and the total price of the order is calculated based on the unit price and quantity.

-- Create Table with computed column

IF OBJECT_ID('orederscomputed', 'U') IS NOT NULL

  DROP TABLE [dbo].[orederscomputed]

GO

CREATE TABLE [dbo].[orederscomputed]

  (

  [OrderID] INT PRIMARY KEY NOT NULL,

  [OrderName] VARCHAR(50) NOT NULL,

  [OrderedQuantity] INT NOT NULL,

  [unitPrice] DECIMAL(16,2) NOT NULL,

  [OrderedPrice] AS [OrderedQuantity]*[unitPrice] PERSISTED

)

GO

--Insert sample data

INSERT INTO orederscomputed (OrderID, OrderName, OrderedQuantity,unitPrice)

SELECT 1, 'Fans',2, 500.00 UNION ALL

SELECT 2, 'Tables', 5,100.00 UNION ALL

SELECT 3, 'laptops', 10,1000.00 UNION ALL

SELECT 4, 'Chairs', 10,120.00 U

GO

-- Check the computed columns

SELECT * FROM dbo.orederscomputed

GO

OUTPUT:

OrderID	OrderName	OrderedQuantity	unitPrice	OrderedPrice
1	Fans	2	500.00	1000.00
2	Tables	5	100.00	500.00
3	laptops	10	1000.00	10000.00
4	Chairs	10	120.00	1200.00