SQL Server 2008：自定义聚合函数和索引视图
我在使用自定义 CLR 聚合函数的视图上创建索引时遇到问题。
我没有看到任何方法将聚合函数标记为确定性函数或具有模式绑定。
我正在像这样创建我的函数：
CREATE ASSEMBLY StringUtil
AUTHORIZATION dbo
FROM 'C:\StringUtil.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE AGGREGATE SUMSTRING (@input nvarchar(200))
RETURNS nvarchar(max) WITH SCHEMABINDING
EXTERNAL NAME StringUtil.Concatenate
我的视图定义为：
CREATE VIEW RolledValues WITH SCHEMABINDING
AS
SELECT ID, SumString(ValueText) as Value FROM [dbo].[IDValue]
GROUP BY ID
当我尝试在该视图上创建索引时出现问题：
CREATE UNIQUE CLUSTERED INDEX IDX_RollValues_ID_VALUE on RolledValues (ID)
Error: Cannot create index on view "dbo.RolledValues" because it uses aggregate
"dbo.SumString". Consider eliminating the aggregate, not indexing the view, or
using alternate aggregates.
那么是否可以在索引视图中使用自定义聚合函数？我找不到任何关于此的文档...
I'm having issues creating an index on a view that uses a custom CLR aggregate function.
I don't see any way to flag the aggregate function as deterministic or with schemabinding.
I'm creating my function like so:
CREATE ASSEMBLY StringUtil
AUTHORIZATION dbo
FROM 'C:\StringUtil.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE AGGREGATE SUMSTRING (@input nvarchar(200))
RETURNS nvarchar(max) WITH SCHEMABINDING
EXTERNAL NAME StringUtil.Concatenate
And my view is defined as:
CREATE VIEW RolledValues WITH SCHEMABINDING
AS
SELECT ID, SumString(ValueText) as Value FROM [dbo].[IDValue]
GROUP BY ID
The issue occurs when I try to create an index on that view:
CREATE UNIQUE CLUSTERED INDEX IDX_RollValues_ID_VALUE on RolledValues (ID)
Error: Cannot create index on view "dbo.RolledValues" because it uses aggregate
"dbo.SumString". Consider eliminating the aggregate, not indexing the view, or
using alternate aggregates.
So is it possible to use a custom aggregate function in an indexed view? I cannot find any documentation on this...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
创建索引视图页面列出了许多限制：
目前甚至没有规定将 CLR 聚合描述为确定性（至少，如果 API 想要保持一致的话）。 SqlFunctionAttribute 具有
IsDeterministic< /代码> 属性。 SqlUserDefinedAggregateAttribute 中不存在此类属性，它确实有帮助如果您考虑为什么索引视图存在如此多的限制，就可以对事情进行推理。
关于聚合的解释非常简单 - 只允许使用具有 SQL Server 能够执行的属性的聚合（例如
SUM和COUNT_BIG）完全基于作为当前事务主题的行子集来调整值，或者从索引中添加或删除行。例如，如果视图中有一行
ID=19、COUNT_BIG=5 和SUM=96，并且事务删除 3 行 < code>ID 19，其SUM添加到 43，则可以将视图的该行更新为COUNT_BIG=2 和SUM=53。或者，如果事务删除了ID=19 的 5 行，则会导致该行被删除。请注意，无论哪种情况，我们都不必检查表的任何其他行来确定它们是否具有
ID=19。那么 SQL Server 如何希望通过用户定义的聚合来实现类似的功能呢？用户定义聚合的当前接口没有您需要的那种支持（它还需要有一个类似接口的触发器）。
The page on Creating Indexed Views lists a number of restrictions:
There's not even provision, at the current time, to describe a CLR aggregate as Deterministic (at least, if the API was going to be consistent). The SqlFunctionAttribute has an
IsDeterministicproperty. No such property exists in SqlUserDefinedAggregateAttributeIt does help to reason about things if you consider why so many restrictions exist on indexed views.
The ones on aggregates have a pretty simple explanation - you're only allowed to use aggregates (such as
SUMandCOUNT_BIG) that have the property that SQL Server will be able to adjust the values, or add or remove rows from the index, based purely on the subset of rows that are the subject of the current transaction.E.g. if the view has a row with
ID=19,COUNT_BIG=5, andSUM=96, and a transaction deletes 3 rows withID19, whoseSUMadds to 43, then it can update that row of the view to beCOUNT_BIG=2 andSUM=53. Alternatively, if the transaction had deleted 5 rows withID=19, it would have caused the row to be removed.Note that in either case, we don't have to examine any other rows of the table to determine if they have
ID=19.So how could SQL Server hope to achieve similar functionality with a user defined aggregate? The current interface for user defined aggregates doesn't have the sort of support you'd need (it would need to have a trigger like interface also).