GarethD's questions

Eu tenho uma coluna computada persistente em uma tabela que é simplesmente composta de colunas concatenadas, por exemplo

CREATE TABLE dbo.T 
(   
    ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D DATE NULL,
    E VARCHAR(20) NULL,
    Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL 
);

Isso Compnão é exclusivo e D é a data de início válida de cada combinação de A, B, C, portanto, uso a seguinte consulta para obter a data final de cada uma A, B, C(basicamente a próxima data inicial para o mesmo valor de Comp):

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

Em seguida, adicionei um índice à coluna computada para auxiliar nesta consulta (e também em outras):

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

O plano de consulta, no entanto, me surpreendeu. Eu teria pensado que, como tenho uma cláusula where informando isso D IS NOT NULLe estou classificando por Comp, e não fazendo referência a nenhuma coluna fora do índice, que o índice na coluna computada poderia ser usado para digitalizar t1 e t2, mas vi um índice agrupado Varredura.

Então forcei o uso desse índice para ver se dava um plano melhor:

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

Que deu este plano

Isso mostra que uma pesquisa de chave está sendo usada, cujos detalhes são:

Agora, de acordo com a documentação do SQL-Server:

Você pode criar um índice em uma coluna computada definida com uma expressão determinística, mas imprecisa, se a coluna estiver marcada como PERSISTED na instrução CREATE TABLE ou ALTER TABLE. Isso significa que o Mecanismo de Banco de Dados armazena os valores calculados na tabela e os atualiza quando quaisquer outras colunas das quais a coluna calculada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes ao criar um índice na coluna e quando o índice é referenciado em uma consulta. Essa opção permite criar um índice em uma coluna computada quando o Mecanismo de Banco de Dados não puder provar com precisão se uma função que retorna expressões de coluna computada, particularmente uma função CLR criada no .NET Framework, é determinística e precisa.

Portanto, se, como os documentos dizem "o mecanismo de banco de dados armazena os valores calculados na tabela" , e o valor também está sendo armazenado em meu índice, por que é necessária uma pesquisa de chave para obter A, B e C quando eles não são referenciados em a consulta em tudo? Eu suponho que eles estão sendo usados ​​para calcular o Comp, mas por quê? Além disso, por que a consulta pode usar o índice em t2, mas não em t1?

Consultas e DDL no SQL Fiddle

NB, marquei o SQL Server 2008 porque esta é a versão em que meu principal problema está, mas também recebo o mesmo comportamento em 2012.