Computed column是一个虚拟列,在其所属表中并无物理存在,除非我们将其标为PERSISTED; 它用来存储其所属表的其他列经过计算的值。
Comupted column 不能有默认值、不能有外键关联也不能接收NOT NULL限制;因为它的值取决于其他列的计算值,所以我们很容易理解它不能接收插入新值和修改。Computed column 可以和其他列一起作为主键也可以加UNIQUE修饰,可以加为索引列,当其为索引列的时候需要注意的是,如果参加计算的某一项是可变的,比如时间,那就不能作为 索引列。
添加Computed column的两种方式:
1. 定义表时:
IF EXISTS(select * from sys.objects where object_id = OBJECT_ID('Inventory'))
BEGIN
DROP TABLE Inventory
END
GO
CREATE TABLE Inventory
(
ItemID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL,
TotalItems as itemsInStore + ItemsInWarehouse -- PERSISTED
)
Insert into Inventory (ItemsInStore,ItemsInWarehouse) values( 2,3);
Insert into Inventory (ItemsInStore,ItemsInWarehouse) values( 4,3);
GO
select * from Inventory;
2. 为表添加新列:
IF EXISTS(select * from sys.objects where object_id = OBJECT_ID('Inventory'))
BEGIN
DROP TABLE Inventory
END
GO
CREATE TABLE Inventory
(
ItemID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL
)
GO
Insert into Inventory (ItemsInStore,ItemsInWarehouse) values( 2,3);
Insert into Inventory (ItemsInStore,ItemsInWarehouse) values( 4,3);
GO
ALTER TABLE Inventory ADD TotalItems AS itemsInStore + ItemsInWarehouse -- PERSISTED ;
GO
select * from Inventory;