Início / dba / Perguntas / 339700
Accepted
deadwards
Asked: 2024-05-22 18:39:40 +0800 CST

Visualização indexada - Não é possível criar índice porque "a lista de seleção da visualização contém uma expressão no resultado da função agregada ou coluna de agrupamento."

  • 772

Tenho uma visão bastante simples que estou tentando indexar.

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

Com o índice como

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

A função ( GetThirdAddressPart) funciona da seguinte maneira

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

Executar o seguinte na função acima parece confirmar que a função deve funcionar bem

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;

Dá-me...

query_function_result

Eu verifiquei e isso atende aos requisitos para que uma UDF seja usada.

Se eu executar isso localmente no SQL Server executando através do Docker ( 2022:latest), recebo o seguinte erro

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.

O que é estranho é que funciona se eu executá-lo no Banco de Dados SQL do Azure, o índice é criado sem problemas. Mesmos níveis de compatibilidade em ambos os bancos de dados.

O problema local é com o UDF, se eu comentar isso me permitirá indexá-lo.

  1. Existe algum motivo para a diferença de comportamento do SQL Server em execução por meio do Docker versus o Banco de Dados SQL do Azure? A documentação não parece sugerir que deveria haver.
  2. Tem alguma maneira de resolver isso? Sei que poderia adicionar a função como uma coluna computada à tabela original, só estava tentando evitá-la.

Versões 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
sql-server

2 respostas

  1. Best Answer

    Bem, a mensagem de erro está tecnicamente correta:

    porque a lista de seleção da visão contém uma expressão na [...] coluna de agrupamento

    A expressão é a sua função, que leva Address como parâmetro, e aparece na select list .
    Endereço é uma coluna na GROUP BYcláusula.

    Como resultado,

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

    ...retorna falso no SQL Server 2022 CU12. Pode retornar verdadeiro no Banco de Dados SQL do Azure.

    As bases de código têm muito em comum, mas não são iguais.

    Esse resultado representa a decisão final sobre a indexabilidade da visualização.

    Removendo o problema

    Uma visão como:

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

    ... é indexável, embora não seja diretamente adequado ao seu caso.

    Gambiarra

    Qualquer alteração que evite literalmente as condições da mensagem de erro funcionará, por exemplo, alterando a coluna de agrupamento:

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

    Talvez eles tenham melhorado os testes de indexabilidade no Banco de Dados SQL do Azure.

    Limitação de inlining de função

    Observe que sua função não será incorporada a nenhuma instrução INSERT, UPDATE, DELETEou MERGEexecutada na tabela base referenciada pela visualização, devido às limitações atuais . Sua função pode ser chamada mais de uma vez para cada linha alterada na tabela base, pois a visualização materializada é mantida automaticamente.

    Se isso for uma preocupação para você, considere incorporar manualmente a função usando uma expressão um pouco complicada:

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

  2. Palpite ousado

    Não tenho tempo para construir e testar uma reprodução, mas suspeito que isso se deva ao UDF inlining . Você pode tentar recriar sua função dessa maneira e ver se isso resolve o problema?

    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;

    Consulte também KB4538581 – CORREÇÃO: problemas de inlining de UDF escalar no SQL Server 2022 e 2019

    • 3

relate perguntas