什么时候确定计算列的值?


何时检索值?
何时更改值?
另一些时间?

我猜这是一个新手问题,因为我没有在搜索中找到任何内容。

#1 楼

这取决于您如何定义计算列。将计算一个PERSISTED计算列,然后将其存储为表内的数据。如果您未将列定义为PERSISTED,则将在运行查询时计算该列。

请参见Aaron的回答以获取详细的解释和证明。

品纳·戴夫(Pinal Dave)也对此进行了详细描述并在其系列文章中显示了存储证明:

SQL SERVER –计算列–持久化和存储

评论


如果它们被保留但查询计划使用的索引不覆盖该列怎么办?我不确定您是否会进行查找,或者只是进行实时计算而无法进行当前测试。

–马丁·史密斯
16-4-27的14:14

@Martin你是对的,在我的测试中,SQL Server选择了通过查找重新计算。

–亚伦·伯特兰(Aaron Bertrand)
16-4-27的14:43

#2 楼

自己证明很容易。我们可以创建一个带有计算列的表,该表使用标量用户定义的函数,然后在更新和选择之前和之后检查计划和函数状态,并查看何时记录执行。
假设我们有此函数:
CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT 'XX' + SUBSTRING(@x, 3, LEN(@x)-4) + 'XXXX');
END
GO

和此表:
CREATE TABLE dbo.Floobs
(
  FloobID int IDENTITY(1,1),
  Name varchar(32),
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)),
  CONSTRAINT pk_Floobs PRIMARY KEY(FloobID),
  CONSTRAINT ck_Name CHECK (LEN(Name)>=8)
);
GO

在插入之前和之后检查sys.dm_exec_function_stats(SQL Server 2016和Azure SQL数据库中的新增功能),然后进行选择:
SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

INSERT dbo.Floobs(Name) VALUES('FrankieC');

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

SELECT * FROM dbo.Floobs;

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

我在插入项上没有调用任何函数,仅在选择项上调用。
DROP TABLE dbo.Floobs;
GO
DROP FUNCTION dbo.mask;
GO

...
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)) PERSISTED,
...

我看到相反的事情:我在插入而不是select上记录了执行。
没有足够现代的SQL Server版本可以使用PERSISTED吗?不用担心,这也会在执行计划中捕获。
对于非持久版本,我们只能在select中看到引用的功能:




虽然持久化版本仅显示插入时发生的计算:




现在,Martin在评论:并非总是如此。让我们创建一个不覆盖持久化计算列的索引,并运行使用该索引的查询,看看查找是否从现有持久化数据中获取数据,或者在运行时计算数据(删除并重新创建函数)和表在这里):
CREATE INDEX x ON dbo.Floobs(Name);
GO

INSERT dbo.Floobs(name) 
  SELECT LEFT(name, 32) 
  FROM sys.all_columns 
  WHERE LEN(name) >= 8;

现在,我们将运行一个使用索引的查询(实际上,在这种情况下,即使没有where子句,它也默认使用索引):
SELECT * FROM dbo.Floobs WITH (INDEX(x))
  WHERE Name LIKE 'S%';

我在函数统计信息中看到了其他执行,而该计划并未说谎:



因此,答案是IT依赖。在这种情况下,SQL Server认为重新计算值比执行查找要便宜。由于各种因素,这可能会改变,因此请不要依赖它。无论是否使用用户定义的函数,这都可能在任一方向发生;我只在这里使用它是因为它使说明变得更加容易。

评论


非常感谢,我从不质疑引擎在计算结果中的行为。

– Arthur D
16-4-27的15:07

@ArthurD这是一个优化程序的决定(主要是基于每个替代方法的估计成本),请参见我对另一个问题的回答。

–保罗·怀特♦
16年4月27日在15:08

#3 楼

这个问题的答案确实是“取决于”。我刚刚遇到了一个示例,其中SQL Server使用持久化的计算列上的索引,但它仍在执行该函数,就好像这些值从一开始就没有持久化。它可能与列的数据类型(nvarchar(37))或表的大小(大约700万行)有关,但是在这种特殊情况下,SQL Server决定忽略persisted关键字。 >
在这种情况下,表上的主键是TransactionID,它也是一个计算得出的持久化列。执行计划正在生成索引扫描,并且在只有700万行的表中,此简单查询要花费2-3分钟的时间才能运行,因为该函数在每一行上都再次运行,并且值似乎没有持久化索引。