【SqlServer】计算列(Computed Columns)使用案例

Sql Server提供的计算列是一个虚拟的列，通常情况下该列的值是由表中的其它列计算得出的。默认情况下，它不占用磁盘容量，因为这些计算列都是根据指定的表达式动态计算出来的，只有查询的时候才会进行计算出来。然而，如果使用了persisted关键字的计算列，该关键词会将表达式的计算结果写入到磁盘中，并进行持久化存储。同时，也可以在计算列上建立索引，约束，或外键等。

 

场景举例：

a. 提高JSON数据查询的效率。假如jsontxt字段中存储着一系列键值对，如果要查询某一个键对应的值，那么就要使用JSON_VALUE函数  JSON_VALUE(jsontxt,'$."your key"') 。当你每一次用JSON_VALUE函数时，都会把整个jsontxt字段加载到内存中，然后再找到 your key 的值。当jsontxt数据增大后，这个查询将会逐渐变慢。

b. 承担表中字段的计算任务。比如：一个员工表[employees]，每一个员工都有一个出生日期[birthday]字段，那么年龄字段[age]的值就可以被动态计算出来了，计算表达式：  datediff(year,birthday,getdate()) 。再例如订单表，订单的支付金额可以根据产品价格，产品数量，运费，优惠金额，以及 税费 等字段计算得出。

 

你可以通过SqlServer Management Studio的UI界面操作来指定计算列，也可以通过Transact-SQL来指定计算列。

在Sql Server Management Studio中， 右键列 -》modify -》 新建列 -》Computed Column Specification  中来指定计算列。

 

通过Transact-SQL 来指定计算列，格式为： 列名 as 表达式 。

-- 在创建表的时候指定为计算列
create table Employees(
birthday datetime,
age as datediff(year,birthday,getdate()));


-- 在已经创建的表上添加计算列
create table Employees(birthday datetime);
alter table Employees add age as datediff(year,birthday,getdate());

 

若要指定计算列为持久化存储（Persisted）, 那么表达式中的计算结果必需是确定性的。比如上面的Age使用了getdate() 函数，这使得Age变成不确定性的，因此不能持久化Age，以及不能在Age上建立索引。

 指定计算列为持久化存储只需要在后面加上persisted关键字即可，例如：

-- 在创建表的时候指定计算列
create table Products(
    id INT NOT NULL IDENTITY PRIMARY KEY,
    name nvarchar(50) not null,
    jsontxt nvarchar(4000) not null,
    [side effect] as JSON_VALUE(jsontxt,'$."side effect"') persisted
);

-- 在已经创建的表上添加计算列
create table Products(
    id INT NOT NULL IDENTITY PRIMARY KEY,
    name nvarchar(50) not null,
    jsontxt nvarchar(4000) not null

);
alter table Products add [side effect] as JSON_VALUE(jsontxt,'$."side effect"') persisted;

表Products中的jsontxt字段存储的是JSON格式的键值对，是产品的详细参数，side effect是产品中的某一个参数。为了方便管理，才将这些参数整体封装在jsontxt字段中。

-- 插入测试数据
insert into Products(name,jsontxt) values(
'Pfizer–BioNTech COVID-19 vaccine',
'{
  "storage":"10℃",
  "type":"vaccine",
  "target":"Coronavirus",
  "side effect":"Tiredness,Headache"
 }');

计算列除了不能执行Insert, Update外，其余的和普通列一样，直接使用计算列的列名即可：

-- 查询数据
select [side effect] from Products where [side effect] like '%headache%';
-- output: Tiredness,Headache

-- 更新jsontxt中的值
update Products set jsontxt = JSON_MODIFY(jsontxt,'$."side effect"', 'Tiredness,Headache,Muscle pain,Chills,Fever');

-- 再次查询数据
select [side effect] from Products where [side effect] like '%headache%';
-- output: Tiredness,Headache,Muscle pain,Chills,Fever

 

注意：

1. 计算列不能指定INSERT, 和 UPDATE语句。
2. persisted关键字不能用于含有不确定性的计算列中。
3. 只有指定了persisted关键字的计算列，才会持久化存储。如果没指定persisted关键字，那么每次查询计算列都会重新计算一遍表达式。
4. 在创建计算列的时候，我们通常需要进行一些操作以便生成我们期望的值。这个过程中，常常需要用到SQL Server的内置函数，但并不是所有的SQL Server内置函数都是 确定性的（Deterministic），只有全是 确定性类型（Deterministic） 函数生成的动态计算列，才可以进行持久化存储。SQL Server数据库中所有Deterministic的内置函数列表。

参考文献：

1. Specify Computed Columns in a Table

2. Deterministic and Nondeterministic Functions