SQL Server计算列

计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数,也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。

  例如,在 AdventureWorks 示例数据库中,Sales.SalesOrderHeader 表的 TotalDue 列具有以下定义:TotalDue AS Subtotal + TaxAmt + Freight

  除非另行指定,否则计算列是未实际存储在表中的虚拟列。每当在查询中引用计算列时,都将重新计算它们的值。数据库引擎在 CREATE TABLE 和 ALTER TABLE 语句中使用 PERSISTED 关键字来将计算列实际存储在表中。如果在计算列的计算更改时涉及任何列,将更新计算列的值。通过将计算列标记为 PERSISTED,可以对具有确定性但不精确的计算列创建索引。另外,如果计算列引用 CLR 函数,则数据库引擎不能验证该函数是否真正具有确定性。在这种情况下,计算列必须为 PERSISTED,以便可对其创建索引。

  计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何可使用正则表达式的其他位置,但下列情况除外:

  用作 CHECK、FOREIGN KEY 或 NOT NULL 约束的计算列必须标记为 PERSISTED。如果计算列的值由具有确定性的表达式定义,并且索引列中允许使用计算结果的数据类型,则可将该列用作索引中的键列,或者用作PRIMARY KEY 或 UNIQUE 约束的一部分。

  例如,如果表中含有整数列 a 和 b,则可以对计算列 a b 创建索引,但不能对计算列 a + DATEPART(dd, GETDATE()) 创建索引,因为在后续调用中,其值可能发生改变。

  计算列不能作为 INSERT 或 UPDATE 语句的目标。

数据库引擎基于使用的表达式自动确定计算列的为 Null 性。即使只有非空列,大多数表达式的结果也“认为”可为空值,因为下溢或溢出生成的结果也可能为空。使用带 AllowsNull 属性的 COLUMNPROPERTY 函数可查明表中任何计算列的为 Null 性。通过指定 ISNULL (check_expression, constant) 可以将可为空值的表达式转换为不可为空值的表达式,其中constant 是可替换所有空结果的非空

创建表时使用计算列的利弊,计算列持久化的含义

   在数据库表设计的时候有一个比较特殊的列不填写任何设计类型,用户不可以改变该列的值,这就是计算列。计算列的值是通过一定的函数公式等以另一个或多个列的值为输入值,计算出结果。 打开表或在新建表的时候,在列属性下面就有"计算所得的列规范"项,在"公式"中填入需要的公式便完成计算列的设计。

优点:

1、计算列不能引用其他表的列,但是可以通过函数来实现。

2、如果在计算列的计算更改时涉及任何列,将更新计算列的值。通过将计算列标记为 PERSISTED,可以对具有确定性但不精确的计算列创建索引。

3、在sql server中想使用某个数据,而这个数据又不在表中,这时候可以使用计算列。

4、可以将索引建立在计算列上。一旦你创建了计算列,你就可以用几个目录视图来观察和计算与列相关的元数据。

5、在不需要创建视图的情况下,该"虚拟列"就可以作为SELECT语句的一部分进行输出。对于不用"难懂的"代码就实现"行内非规范化"来说,这是种完美的做法。

缺点:

1、       在计算列中不能直接写比较复杂的逻辑,一般要结合自定义函数和计算列,这样就可以完成各种复杂逻辑了。

2、       计算列是未实际存储在表中的虚拟列。每当在查询中引用计算列时,都将重新计算它们的值。

3、       如果计算列引用 CLR 函数,则数据库引擎不能验证该函数是否真正具有确定性。在这种情况下,计算列必须为 PERSISTED,以便可对其创建索引。  

4、       计算列不能作为 INSERT 或 UPDATE 语句的目标。

5、       计算列有一个可以说是危险的功能:如果你在插入数据时忽略了插入字段的列表,则计算列会被略过。SQL Server会忽略任何计算列,逐个匹配别的字段,就好像计算列不存在一样。

计算列持久化的含义

SQL Server 所作的重大改进是提供了在列内自动持久化数据的能力,这样你就不必每次取一行都进行计算。只对使用确定性函数(对相同的输入,它们永远都返回相同的输出)的公式才提供了这种持久化功能。GETDATE()是非确定性的,基于时间不同,它返回不同的日期值;而UPPER是确定性的,只要用相同的参数运行该函数,它每次都返回相同的值。持久化可以提高下性能。

计算列的持久化:物理存储的表的计算列。计算中涉及的任何列更改时,持久化计算列的值即更新。当对某个计算列应用了持久化属性时,如果该列为确定列但不精确,可以对该列创建索引。计算列能用在索引中,但是一定要符合一些条件。比如是确定的(对于一组给定的输入总是返回相同的结果)和精确的(不包含浮点值)

利 对于需要计算值或者通过处理其他列来产生值的情况,我们在SQL Server中有一个更强大有效的功能。这个功能是“计算列”。创建计算列可以改进具体环境中的性能。并且,用户可以根据具体情况,在需要使用持久化技术时,创建持久化计算列。

持久化技术封装了数据访问细节,为大部分业务逻辑提供面向对象的API。

● 通过持久化技术可以减少访问数据库数据次数,增加应用程序执行速度;

● 代码重用性高,能够完成大部分数据库操作;

● 松散耦合,使持久化不依赖于底层数据库和上层业务逻辑实现,更换数据库时只需修改配置文件而不用修改代码

弊 计算列由可以使用同一表中的其他列的表达式计算得来。除非另行指定,否则计算列是未实际存储在表中的虚拟列。每当在查询中引用计算列时,都将重新计算它们的值。如果在计算列的计算更改时涉及任何列,将更新计算列的值。计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何可使用正则表达式的其他位置,但下列情况除外:用作 CHECK、FOREIGN KEY 或 NOT NULL 约束的计算列必须标记为 PERSISTED.计算列不能作为INSERT 或 UPDATE 语句的目标。

### 如何在 SQL Server创建和使用计算 #### 创建计算的方法 在 SQL Server 中,可以通过两种方式定义计算:一种是在设计表结构时通过 **SQL Server Management Studio (SSMS)** 的图形界面完成;另一种是通过编写 **Transact-SQL** 脚本实现。 1. **通过 SSMS 图形界面** 在 SSMS 中新建或修改一张表时,在“属性”的部分找到名为“计算所得的规范”选项卡。在此处输入用于计算值的公式即可[^2]。例如,如果希望基于 `Price` 和 `TaxRate` 动态生成一列表示商品总价格,则可以在公式框中填入 `(Price * TaxRate)` 表达式。 2. **通过 T-SQL 命令** 下面展示了一个具体的例子说明如何利用 T-SQL 来新增加一个计算字段到现有表格里: ```sql ALTER TABLE Products ADD TotalCost AS (UnitPrice * Quantity); ``` 上述语句向 `Products` 表添加了一名叫做 `TotalCost` 的新成员,它并不真正占用物理空间而是依据每条记录里的 `UnitPrice` 乘以 `Quantity` 得出的结果作为显示内容[^1]。 #### 使用持久化(Persisted)提高性能 为了提升查询效率或者支持建立索引等功能需求,可以选择将某些频繁访问又耗时较长运算得出的数据项设置成持久化的状态存储下来而不是每次重新演算出来。这可通过扩展前面提到过的语法形式达成目标: ```sql ALTER TABLE Products ADD TotalCost AS (UnitPrice * Quantity) PERSISTED; ``` 这里的关键字 `PERSISTED` 就是用来指示数据库引擎把每一次更新后的最终数值实际写回到磁盘上去保存起来以便后续更快读取。 #### 关于索引的支持情况 值得注意的是,并不是所有的计算都能够被赋予索引对象。只有那些满足特定条件——比如确定性和精确性的函数所构成的表达式的计算才允许创建相应的索引结构来进一步优化检索速度等问题解决办法[^4]。 ### 示例代码片段 下面给出一段完整的示范程序用来演示整个过程从头至尾的操作步骤: ```sql -- Step 1: Create a sample table without computed column initially. CREATE TABLE Orders ( OrderID INT PRIMARY KEY, UnitPrice MONEY NOT NULL, Quantity SMALLINT NOT NULL ); -- Step 2: Add the computed column 'TotalAmount'. ALTER TABLE Orders ADD TotalAmount AS (UnitPrice * Quantity); -- Optional step: If you want to persist this value physically within your storage system. ALTER TABLE Orders DROP COLUMN IF EXISTS PersistedTotalAmount; -- Ensure no duplicate columns exist before altering again. GO ALTER TABLE Orders ADD PersistedTotalAmount AS (UnitPrice * Quantity) PERSISTED; -- Insert some test data into our orders table now that everything is set up properly. INSERT INTO Orders(OrderID, UnitPrice, Quantity) VALUES(101, CAST('99.99' AS MONEY), 5), (102, CAST('75.00' AS MONEY), 3); SELECT * FROM Orders; ``` 以上脚本首先构建了一个简单的订单表模型不含任何预先设定好的合计金额项目接着再分别追加上去两个版本一个是普通的即时求解型另一个则是经过持久处理之后的形式最后还插入了几笔模拟交易数据供验证查看效果之用。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值