我有一个相当简单的视图,正在尝试索引。
CREATE OR ALTER VIEW [schema].[IndexedView]
WITH SCHEMABINDING AS
SELECT
SUM(ISNULL([Quantity], 0)) as [Quantity],
[Address],
[Asset],
[schema].GetThirdAddressPart([Address]) AS [PortfolioId],
COUNT_BIG(*) AS [Count]
FROM
[schema].[table]
WHERE (
[Address] LIKE ('%:thing')
OR [Address] LIKE ('%:thing2')
OR [Address] LIKE ('%:thing3')
)
GROUP BY [Address], [Asset], [schema].GetThirdAddressPart([Address])
索引为
CREATE UNIQUE CLUSTERED INDEX [IDX_Index] ON [schema].[IndexedView]
(
[Asset] ASC,
[Address] ASC
)
函数 ( GetThirdAddressPart
) 的工作原理如下
CREATE FUNCTION [schema].[GetThirdAddressPart] (@inputString varchar(30))
RETURNS varchar(30) WITH SCHEMABINDING
AS
BEGIN
DECLARE @firstColonPos INT = CHARINDEX(':', @inputString);
DECLARE @secondColonPos INT = CHARINDEX(':', @inputString, @firstColonPos + 1);
DECLARE @thirdColonPos INT = CHARINDEX(':', @inputString, @secondColonPos + 1);
RETURN SUBSTRING(
@inputString,
@secondColonPos + 1,
CASE
WHEN @thirdColonPos = 0 THEN LEN(@inputString)
ELSE @thirdColonPos - @secondColonPos - 1
END
);
针对上述函数运行以下命令似乎支持该函数应该可以正常工作
SELECT ObjectPropertyEx(Object_Id('schema.GetThirdAddressPart'), N'IsDeterministic') AS deterministic,
ObjectPropertyEx(Object_Id('schema.GetThirdAddressPart'), N'IsPrecise') AS precise,
ObjectPropertyEx(Object_Id('schema.GetThirdAddressPart'), N'IsSystemVerified') AS verified,
ObjectPropertyEx(Object_Id('schema.GetThirdAddressPart'), N'UserDataAccess') AS UserDataAccess,
ObjectPropertyEx(Object_Id('schema.GetThirdAddressPart'), N'SystemDataAccess') AS SystemDataAccess;
给我...
我已经检查过,这满足使用 UDF 的要求。
如果我在通过 Docker ( ) 运行的 SQL Server 上本地运行此命令2022:latest
,则会收到以下错误
Msg 8668, Level 16, State 0, Line 34 Cannot create the clustered index 'IDX_Index' on view 'services-import.schema.IndexedView' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.
奇怪的是,如果我在 Azure SQL 数据库中运行它,索引就可以毫无问题地创建。两个数据库的兼容性级别相同。
本地问题在于 UDF,如果我将其注释掉,它将允许我对其进行索引。
- 通过 Docker 运行的 SQL Server 与通过 Azure SQL 数据库运行的行为有何不同?该文档似乎没有表明应该有。
- 我有什么办法可以解决这个问题吗?我意识到我可以将该函数作为计算列添加到原始表中,我只是想避免它。
SQL 版本:
Local:
Microsoft SQL Server 2022 (RTM-CU12) (KB5033663) - 16.0.4115.5 (X64) Mar 4 2024 08:56:10 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>
Azure:
Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 19 2024 18:03:25 Copyright (C) 2022 Microsoft Corporation