Calculated or computed column in SQL

Calculated or Computed column in table uses expression which uses constant and other columns in table. This column is not physically stored and is virtual column unless persistence property is specified.

FACTS :

1. Calculated or computed column cannot be specified with DEFAULT constraints.
2. Calculated or computed column cannot be specified with NOT NULL constraints
3. Calculated or computed column cannot be specified with FOREIGN KEY constraints

Add a Calculated or computed column to a table

CREATE TABLE dbo.CustomerStocks
(
CustomerID int NOT NULL , StockID int NOT NULL
  , NoofStocks int
, UnitPrice money
, Amount AS NoofStocks * UnitPrice
);

-- Insert values into the table.
INSERT INTO dbo.CustomerStocks (CustomerID ,StockID NoofStocks , UnitPrice)
VALUES (25, 1 , 10 2.00);

-- Display the rows in the table.
SELECT CustomerID ,StockID NoofStocks , UnitPrice, Amount
FROM dbo.CustomerStocks ;

Leave a Reply

%d bloggers like this: