SQL Server中使用Persisted关键字的计算字段

　　对于需要计算值或者通过处理其他列来产生值的情况，我们在SQL Server中有一个更强大有效的功能。这个功能是“计算字段”。

　　一个计算列是是从一个可以使用同一张表中的其它列的语句中计算得到的。函数，变量，常数，非计算列名称或者所有这些的任何组合都可以和操作器联合使用来创建一个计算列。在这个技巧中，我们将使用一个执行计算列的例子。

　　我已经在SQL Server 2005和SQL Server 2008中测试了这些脚本。在下面的脚本中，我们将在AdventureWorks数据库中创建一张叫做CCtest的表以及三个字段[empNumb]，[DOBirth]，[DORetirement]。

　　公司要求我们把每个员工的“退休日期”设置为(DOBirth + 60年 - 1天)。我们不是每次在报表中都计算它，也不是每次[DOBirth]更新时都通过一个触发器来更新[DORetirement]列，而现在有一种更好方法把[DORetirement]作为一个计算列创建。由于这个规则可以在任何时候更改，因此我们把它作为一个计算列来执行而不是一个硬编码值。

　　脚本1： 用计算列创建一张表

     USE [AdventureWorks]
　　GO
　　-- Create Table with computed column
　　CREATE TABLE [dbo].[CCtest]
　　(
　　[empNumb] [int] NULL,
　　[DOBirth] [datetime] NULL,
　　[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
　　)
　　GO

你将看到在SSMS中有一个用于新表的设计视图。提供的计算列说明书如下：

　图二

　　现在我们的表CCtest有一个计算列。类似地，我们使用"ALTER TABLE"命令或者使用SSMS打开设计视图中的表并做出更改，从而把一个计算列添加到任何现有的表中。

　　让我们插入一些数据并运行一个查询来测试该计算列的功能。

　　脚本2： 把数据插入到表中

   USE AdventureWorks
　　GO
　　INSERT INTO CCTest (empNumb, DOBirth)
　　SELECT 30 ,'1985-12-13' UNION ALL
　　SELECT 25 ,'1980-11-18' UNION ALL
　　SELECT 21 ,'1978-01-19' UNION ALL
　　SELECT 7 ,'1985-12-13' UNION ALL
　　SELECT 5 ,'1975-07-23'
　　GO
　　SELECT * FROM dbo.CCTest
　　GO

在这里，我们可以看到我们的计算列：

图三

　　为了验证该计算列将在任何更新发生时更新，我们将更新[DOBirth]使[empNumb] = 25。

　　脚本3：更新empNumb 25中的DOBirth

　
   USE AdventureWorks
　　GO
　　UPDATE CCtest
　　SET DOBirth = '1960-03-25'
　　WHERE empnumb = 25
　　GO
　　SELECT * FROM dbo.CCTest
　　WHERE Empnumb = 25
　　GO

　　我们可以看到我们的计算列已经更新了。

• 　Persisted属性

　　你可能注意到，我们在我们的计算列中也使用了属性“Persisted”。计算列中的这个属性在SQL Server 2005及以后的版本中都引进了。它对于任何计算列而言都是重要的，因为很多其他的功能都依赖于它。为了把一个计算列设置为Persisted，它必须是确定的。

• 规则

　　l 如果Persisted属性被关掉了，那么计算列只是虚拟列。该列将没有数据存储到磁盘上，并且这些值每次在一个脚本中参照时都会被计算。如果这个属性北设置成激活的，那么计算列的数据将会存储在磁盘中。

　　l 如果它是Persisted，那么参照列的任何更新将会在计算中自动同步。

　　l 随着其他一些情况的出现，Persisted将被要求在计算列中创建一个索引。

• Nullability

　　一个计算列值的Nullability将由数据库引擎本身来确定。一个非空参照列的结果可能在某些情况下是空值，以便避免可能的溢处。如果需要，那么你可以使用ISNULL(check_expression,，常数)来提供一个NULL结果的替代值。

• 一些限制

　　对于SQL Server 2000，你不能创建一个persisted计算列。

　　你不能从其它表中参照列直接用于一个计算列。

　　你不能在计算列中应用插入或者更新的语句。

　　如果你在你的语句中连接两种不同数据类型的处理器，那么低优先级的处理器将转化为高优先级的处理器。如果潜在的转化是不可能的，那么将产生错误。

• 一个子查询不能被用作一个表达式来创建计算列。

　　计算列可以在SELECT列表，WHERE 或者ORDER BY 从句中使用，也可以当作正常的表达，但是要把一个计算列当作CHECK, FOREIGN KEY或者 NOT NULL约束，那么你需要把它设置成Persisted。

　　要把一个计算列当作主键或者唯一键约束，你需要通过一个确定的表达式来定义它和并且计算列表达的数据类型是可以索引的，但是最好不要这样做

转载自：

http://database.ctocio.com.cn/dbzjdysummary/48/8730048.shtml

文章经作者授权转载，版权归原文作者所有

图片来源于网络，侵权必删！