A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs. You can specify an expression for a computed column in in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL
CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS QtyAvailable * UnitPrice
);
-- Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice) VALUES (25, 2.00), (10, 1.5);
-- Display the rows in the table.
SELECT * FROM dbo.Products;
To add a new computed column to an existing table
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.35);
To Change an existing column to a computed column
ALTER TABLE dbo.Products DROP COLUMN RetailValue;
GO
ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);
Comments
Post a Comment