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

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

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

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

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

<span style="color: rgba(0, 128, 128, 1)">--</span><span style="color: rgba(0, 128, 128, 1)"> 在创建表的时候，就指定为计算列</span>
<span style="color: rgba(0, 0, 255, 1)">create</span> <span style="color: rgba(0, 0, 255, 1)">table</span><span style="color: rgba(0, 0, 0, 1)"> Employees(
birthday </span><span style="color: rgba(0, 0, 255, 1)">datetime</span><span style="color: rgba(0, 0, 0, 1)">,
age </span><span style="color: rgba(0, 0, 255, 1)">as</span> <span style="color: rgba(255, 0, 255, 1)">datediff</span>(<span style="color: rgba(255, 0, 255, 1)">year</span>,birthday,<span style="color: rgba(255, 0, 255, 1)">getdate</span><span style="color: rgba(0, 0, 0, 1)">()));
</span><span style="color: rgba(0, 128, 128, 1)">--</span><span style="color: rgba(0, 128, 128, 1)"> 在已经创建的表上添加计算列</span>
<span style="color: rgba(0, 0, 255, 1)">create</span> <span style="color: rgba(0, 0, 255, 1)">table</span> Employees(birthday <span style="color: rgba(0, 0, 255, 1)">datetime</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 0, 255, 1)">alter</span> <span style="color: rgba(0, 0, 255, 1)">table</span> Employees <span style="color: rgba(0, 0, 255, 1)">add</span> age <span style="color: rgba(0, 0, 255, 1)">as</span> <span style="color: rgba(255, 0, 255, 1)">datediff</span>(<span style="color: rgba(255, 0, 255, 1)">year</span>,birthday,<span style="color: rgba(255, 0, 255, 1)">getdate</span>());

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

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

<span style="color: rgba(0, 0, 255, 1)">create</span> <span style="color: rgba(0, 0, 255, 1)">table</span><span style="color: rgba(0, 0, 0, 1)"> Products(
    id </span><span style="color: rgba(0, 0, 255, 1)">INT</span> <span style="color: rgba(128, 128, 128, 1)">NOT</span> <span style="color: rgba(0, 0, 255, 1)">NULL</span> <span style="color: rgba(255, 0, 255, 1)">IDENTITY</span> <span style="color: rgba(0, 0, 255, 1)">PRIMARY</span> <span style="color: rgba(0, 0, 255, 1)">KEY</span><span style="color: rgba(0, 0, 0, 1)">,
    name </span><span style="color: rgba(0, 0, 255, 1)">nvarchar</span>(<span style="color: rgba(128, 0, 0, 1); font-weight: bold">50</span>) <span style="color: rgba(128, 128, 128, 1)">not</span> <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">,
    jsontxt </span><span style="color: rgba(0, 0, 255, 1)">nvarchar</span>(<span style="color: rgba(128, 0, 0, 1); font-weight: bold">4000</span>) <span style="color: rgba(128, 128, 128, 1)">not</span> <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">,
    </span><span style="color: rgba(255, 0, 0, 1)">[</span><span style="color: rgba(255, 0, 0, 1)">side effect</span><span style="color: rgba(255, 0, 0, 1)">]</span> <span style="color: rgba(0, 0, 255, 1)">as</span> JSON_VALUE(jsontxt,<span style="color: rgba(255, 0, 0, 1)">‘</span><span style="color: rgba(255, 0, 0, 1)">$."side effect"</span><span style="color: rgba(255, 0, 0, 1)">‘</span><span style="color: rgba(0, 0, 0, 1)">) persisted
);</span>

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

<span style="color: rgba(0, 128, 128, 1)">--</span><span style="color: rgba(0, 128, 128, 1)"> 插入测试数据</span>
<span style="color: rgba(0, 0, 255, 1)">insert</span> <span style="color: rgba(0, 0, 255, 1)">into</span> Products(name,jsontxt) <span style="color: rgba(0, 0, 255, 1)">values</span><span style="color: rgba(0, 0, 0, 1)">(
</span><span style="color: rgba(255, 0, 0, 1)">‘</span><span style="color: rgba(255, 0, 0, 1)">Pfizer–BioNTech COVID-19 vaccine</span><span style="color: rgba(255, 0, 0, 1)">‘</span><span style="color: rgba(0, 0, 0, 1)">,
</span><span style="color: rgba(255, 0, 0, 1)">‘</span><span style="color: rgba(255, 0, 0, 1)">{
  "storage":"10℃",
  "type":"vaccine",
  "target":"Coronavirus",
  "side effect":"Tiredness,Headache"
 }</span><span style="color: rgba(255, 0, 0, 1)">‘</span>);

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

<span style="color: rgba(0, 128, 128, 1)">--</span><span style="color: rgba(0, 128, 128, 1)"> 查询数据</span>
<span style="color: rgba(0, 0, 255, 1)">select</span> <span style="color: rgba(128, 128, 128, 1)">*</span> <span style="color: rgba(0, 0, 255, 1)">from</span> Products <span style="color: rgba(0, 0, 255, 1)">where</span> <span style="color: rgba(255, 0, 0, 1)">[</span><span style="color: rgba(255, 0, 0, 1)">side effect</span><span style="color: rgba(255, 0, 0, 1)">]</span> <span style="color: rgba(128, 128, 128, 1)">like</span> <span style="color: rgba(255, 0, 0, 1)">‘</span><span style="color: rgba(255, 0, 0, 1)">%headache%</span><span style="color: rgba(255, 0, 0, 1)">‘</span>;

注意：

1. 计算列不能指定INSERT, 和 UPDATE语句。
2. persisted关键字不能用于含有不确定性的计算列中。
3. 只有指定了persisted关键字的计算列，才会持久化存储。如果没指定persisted关键字，那么每次查询虚拟列都会重新计算一遍表达式。

参考文献：

1. Specify Computed Columns in a Table

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

标签：方便   效率   sele   键值对   head   VID   加载   虚拟   persist