5

I have a fairly simple view I'm trying to index.

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])

With the index as

CREATE UNIQUE CLUSTERED INDEX [IDX_Index] ON [schema].[IndexedView]
(
    [Asset] ASC,
    [Address] ASC
)

The function (GetThirdAddressPart) works in the following way

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
           );

Running the following against the above function seems to support that the function should work OK

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;

Gives me...

query_function_result

I've checked and this meets the requirements for a UDF to be used.

If I run this locally on SQL Server running through Docker (2022:latest), I get the following error

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.

What is weird is that works if I run it in Azure SQL Database, the index is created without issue. Same compatibility levels on both databases.

The issue locally is with the UDF, if I comment that out, it will allow me to index it.

  1. Is there any reason for the difference in behaviour the SQL Server running through Docker vs. Azure SQL Database? The documentation does not seem to suggest there should be.
  2. Any way I can resolve this? I realise I could add the function as a computed column to the original table, I was just trying to avoid it.

SQL Versions:

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 
0

2 Answers 2

8

Well, the error message is technically correct:

because the select list of the view contains an expression on [...] grouping column

The expression is your function, which takes Address as a parameter, and appears in the select list.
Address is a column in the GROUP BY clause.

As a result,

OBJECTPROPERTY(OBJECT_ID(<View4U>, 'V'), 'IsIndexable')

...returns false on SQL Server 2022 CU12. It might return true on Azure SQL Database.

The code bases share a lot in common, but they're not the same.

That result represents the final decision on view indexability.

Removing the problem

A view like:

CREATE OR ALTER VIEW <View4U>
WITH SCHEMABINDING AS
SELECT
    SUM(ISNULL([Quantity], 0)) as [Quantity],
    --[T].[Address],
    [T].[Asset],
    dbo.GetThirdAddressPart([T].[Address]) AS [PortfolioId],
    COUNT_BIG(*) AS [Count]
FROM
    <Table4U> AS T
WHERE 
    [T].[Address] LIKE ('%:thing')
    OR [T].[Address] LIKE ('%:thing2')
    OR [T].[Address] LIKE ('%:thing3')
GROUP BY 
    --[T].[Address], 
    [T].[Asset], 
    dbo.GetThirdAddressPart([T].[Address]);

...is indexable, though it's not directly suitable in your case.

Workaround

Any change that literally avoids the conditions in the error message will work, for example by changing the grouping column:

CREATE OR ALTER VIEW dbo.View4U
WITH SCHEMABINDING AS
SELECT
    SUM(ISNULL([Quantity], 0)) as [Quantity],
    [Address] = RTRIM([T].[Address]), -- !CHANGED!
    [T].[Asset],
    dbo.GetThirdAddressPart([T].[Address]) AS [PortfolioId],
    COUNT_BIG(*) AS [Count]
FROM
    <Table4U> AS T
WHERE 
    [T].[Address] LIKE ('%:thing')
    OR [T].[Address] LIKE ('%:thing2')
    OR [T].[Address] LIKE ('%:thing3')
GROUP BY 
    RTRIM([T].[Address]), -- !CHANGED!
    [T].[Asset], 
    dbo.GetThirdAddressPart([T].[Address]);
GO
-- Succeeds
CREATE UNIQUE CLUSTERED INDEX [IDX_Index] 
ON dbo.<View4U>
(
    [Asset] ASC,
    [Address] ASC
);

Perhaps they've improved the indexability tests in Azure SQL Database.

Function inlining limitation

Please note your function will not be inlined for any INSERT, UPDATE, DELETE, or MERGE statements executed against the base table referenced by the view, due to the current limitations. Your function may be called more than once for each row changed in the base table as the materialized view is automatically maintained.

If this is a concern for you, consider manually inlining the function using a slightly unwieldly expression:

CREATE OR ALTER VIEW dbo.<View4U>
WITH SCHEMABINDING AS
SELECT
    Quantity = SUM(ISNULL(T.Quantity, 0)),
    [Address] = RTRIM(T.[Address]),
    T.Asset,
    PortfolioId = SUBSTRING(T.[Address], CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address]) + 1) + 1, ISNULL(NULLIF(CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address]) + 1) + 1), 0), LEN(T.[Address]) + 1) - (CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address]) + 1) + 1)),
    [Count] = COUNT_BIG(*)
FROM
    <Table4U> AS T
WHERE 
    T.[Address] LIKE ('%:thing')
    OR T.[Address] LIKE ('%:thing2')
    OR T.[Address] LIKE ('%:thing3')
GROUP BY 
    RTRIM(T.[Address]), 
    T.Asset,
    SUBSTRING(T.[Address], CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address]) + 1) + 1, ISNULL(NULLIF(CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address]) + 1) + 1), 0), LEN(T.[Address]) + 1) - (CHARINDEX(':', T.[Address], CHARINDEX(':', T.[Address]) + 1) + 1));
0
3

wild guess

I don't have time to build and test a repro, but I suspect that this is due to UDF inlining. Can you try re-creating your function this way, and seeing if that resolves the issue?

CREATE FUNCTION [schema].[GetThirdAddressPart] (@inputString varchar(30))
RETURNS varchar(30) WITH 
    SCHEMABINDING,
    INLINE = OFF /*This is new*/
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
           );
END;

See also KB4538581 - FIX: Scalar UDF Inlining issues in SQL Server 2022 and 2019

1
  • Unfortunately, it did not work for me, nice idea though! Same error message
    – deadwards
    Commented May 22 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.