Não é possível criar um índice filtrado em uma coluna computada

Infelizmente, a partir do SQL Server 2014, não há capacidade de criar um Filtered Indexonde o filtro esteja em uma coluna computada (independentemente de ser ou não persistente).

Há um Connect Item aberto desde 2009, então vá em frente e vote nele. Talvez a Microsoft conserte isso um dia.

Aaron Bertrand tem um artigo que aborda vários outros problemas com índices filtrados .

Embora você não possa criar um índice filtrado em uma coluna persistente, há uma solução bastante simples que você pode usar.

Como teste, criei uma tabela simples com uma IDENTITYcoluna e uma coluna computada persistente com base na coluna de identidade:

USE tempdb;

CREATE TABLE dbo.PersistedViewTest
(
    PersistedViewTest_ID INT NOT NULL
        CONSTRAINT PK_PersistedViewTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(2000) NOT NULL
    , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
);
GO

Em seguida, criei uma visualização vinculada ao esquema com base na tabela com um filtro na coluna computada:

CREATE VIEW dbo.PersistedViewTest_View
WITH SCHEMABINDING
AS
SELECT PersistedViewTest_ID
    , SomeData 
    , TestComputedColumn
FROM dbo.PersistedViewTest
WHERE TestComputedColumn < CONVERT(INT, 27);

Em seguida, criei um índice clusterizado na exibição vinculada ao esquema, que tem o efeito de persistir os valores armazenados na exibição, incluindo o valor da coluna computada:

CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
GO

Insira alguns dados de teste na tabela:

INSERT INTO dbo.PersistedViewTest (SomeData)
SELECT o.name + o1.name + o2.name
FROM sys.objects o
    CROSS JOIN sys.objects o1
    CROSS JOIN sys.objects o2;

Crie um item de estatísticas e um índice na exibição:

CREATE STATISTICS ST_PersistedViewTest_View
ON dbo.PersistedViewTest_View(TestComputedColumn)
WITH FULLSCAN;

CREATE INDEX IX_PersistedViewTest_View_TestComputedColumn
ON dbo.PersistedViewTest_View(TestComputedColumn);

A execução SELECTde instruções na tabela com a coluna persistente agora pode usar automaticamente a exibição persistente, se o otimizador de consulta determinar que faz sentido fazer isso:

SELECT pv.PersistedViewTest_ID
    , pv.TestComputedColumn
FROM dbo.PersistedViewTest pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)

O plano de execução real para a consulta acima mostra que o otimizador de consulta escolheu usar a exibição persistente para retornar os resultados:

Você deve ter notado a conversão explícita na WHEREcláusula acima. Esse explícito CONVERT(INT, 26)permite que o otimizador de consulta use corretamente o objeto de estatísticas para estimar o número de linhas que serão retornadas pela consulta. Se escrevermos a consulta com WHERE pv.TestComputedColumn = 26, o otimizador de consulta pode não estimar adequadamente o número de linhas, pois 26 é realmente considerado um TINY INT; isso pode fazer com que o SQL Server não use a exibição persistente. As conversões implícitas podem ser muito dolorosas e vale a pena usar consistentemente os tipos de dados corretos para comparações e uniões.

É claro que todas as "pegadinhas" padrão resultantes do uso de schema binding se aplicam ao cenário acima; isso pode impedir o uso dessa solução alternativa em todos os cenários. Por exemplo, não será mais possível modificar a tabela base sem primeiro remover a vinculação do esquema da exibição. Para fazer isso, você precisará remover o índice clusterizado da exibição.

Se você não tiver o SQL Server Enterprise Edition, o otimizador de consulta não usará automaticamente a exibição persistente para consultas que não fazem referência direta à exibição usando a WITH (NOEXPAND)dica. Para obter o benefício de usar a exibição persistente em versões não Enterprise Edition, você precisará reescrever a consulta acima para algo como:

SELECT pv.PersistedViewTest_ID
    , pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
WHERE pv.TestComputedColumn = CONVERT(INT, 26)

Obrigado a Ian Ringrose por apontar a limitação da Enterprise Edition acima e a Paul White pela (NOEXPAND)dica.

Esta resposta de Paul tem alguns detalhes interessantes sobre o otimizador de consulta em relação a visualizações persistentes.

De Create Indexe sua wherecláusula, isso não é possível:

ONDE

Cria um índice filtrado especificando quais linhas incluir no índice. O índice filtrado deve ser um índice não clusterizado em uma tabela. Cria estatísticas filtradas para as linhas de dados no índice filtrado.

O predicado do filtro usa lógica de comparação simples e não pode fazer referência a uma coluna computada, uma coluna UDT, uma coluna de tipo de dados espaciais ou uma coluna de tipo de dados hierarquiaID. Comparações usando literais NULL não são permitidas com os operadores de comparação. Em vez disso, use os operadores IS NULL e IS NOT NULL.

Fonte: MSDN

• Você precisa de uma coluna que não seja calculada para colocar o índice filtrado.
• Você precisa calcular o valor para ir nessa coluna.

Antes de calcularmos as colunas, usávamos gatilhos para calcular o valor das colunas sempre que a linha era alterada ou inserida.

(Um gatilho também pode ser usado para inserir/remover o PK do item de uma 2ª tabela que foi usada em consultas.)

Esta é uma tentativa de melhorar o trabalho de Hannah . Em sua solução, ela sugere o uso de 2 índices na exibição e um objeto de estatísticas.

O primeiro índice é clusterizado, o que é realmente necessário, pois, ao contrário de um índice não clusterizado em uma tabela, um erro será gerado se a criação de um índice não clusterizado na exibição for tentada sem primeiro ter um índice clusterizado.

O segundo índice é um índice não clusterizado, usado como o índice por trás da consulta. Na seção de comentários de sua resposta, perguntei o que aconteceria se um índice clusterizado fosse usado em vez de um índice não clusterizado.

A análise a seguir tenta responder a essa pergunta.

Estou usando exatamente o mesmo código, exceto que não estou criando um índice não clusterizado na exibição.

Também não estou criando um objeto de estatísticas. Se você estiver acompanhando e usando o SQL Server Management Studio (SSMS) para inserir o código abaixo, saiba que poderá ver algumas linhas onduladas vermelhas - que parecem erros. Estes (provavelmente) não são erros, mas envolvem um problema com o intellisense.

Você pode desabilitar o intellisense ou simplesmente ignorar os erros e executar os comandos. Eles devem ser concluídos sem erros.

-- Create the test table that uses a computed column.
USE tempdb;
CREATE TABLE dbo.PersistedViewTest
(
    PersistedViewTest_ID INT NOT NULL
    CONSTRAINT PK_PersistedViewTest
    PRIMARY KEY CLUSTERED
    IDENTITY(1,1)
    , SomeData VARCHAR(2000) NOT NULL
    , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
);
GO

-- Insert some test data into the table.
INSERT INTO dbo.PersistedViewTest (SomeData)
SELECT o.name + o1.name + o2.name
FROM sys.objects o
    CROSS JOIN sys.objects o1
    CROSS JOIN sys.objects o2;
GO

O seguinte plano de execução (sem visualização/visualização de índice) é produzido depois que a seguinte consulta é executada na tabela:

SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO

Isso fornece uma linha de base para comparação. Observe que após a conclusão da consulta, um objeto de estatísticas foi criado (_WA_Sys_00000003_1FCDBCEB). O objeto de estatísticas PK_PersistedViewTest foi criado quando o índice de tabela em cluster foi criado.

Em seguida, a exibição filtrada e o índice clusterizado nessa exibição são criados:

-- Create filtered view on the computed column.
CREATE VIEW dbo.PersistedViewTest_View
WITH SCHEMABINDING
AS
SELECT PersistedViewTest_ID, SomeData, TestComputedColumn
FROM dbo.PersistedViewTest
WHERE TestComputedColumn < CONVERT(INT, 27);
GO

-- Create unique clustered index to persist the values, including the computed column.
CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
GO

Agora, vamos tentar executar a consulta novamente, mas desta vez contra a exibição:

SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO

O novo plano de execução é agora:

Se o novo plano for confiável, após a adição da exibição e do índice clusterizado nessa exibição, as estatísticas parecem indicar que o tempo necessário para executar a consulta dobrou. Além disso, observe que nenhum novo objeto de estatísticas foi criado para suportar o novo índice após a execução da consulta, o que é diferente da consulta na tabela.

O plano de consulta ainda sugere que a criação de um índice não clusterizado seria bastante útil para melhorar o desempenho da consulta. Então, isso significa que um índice não clusterizado deve ser adicionado à exibição antes que a melhoria de desempenho desejada possa ser obtida? Há uma última coisa a tentar. Modifique a consulta para usar a opção "WITH NOEXPAND":

SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO

Isso resulta no seguinte plano de consulta:

This execution plan looks quite similar to the one that was produced with the nonclustered index given in Hannah's answer. But, this one is done with one less (nonclustered) index and one less statistics object.

It turns out that the NOEXPAND option has to be used with the express and standard versions of SQL Server to make proper use of an indexed view. Paul White has an excellent article that expounds on the benefits of using the NOEXPAND option. He also recommends this option be used with the enterprise edition to ensure the uniqueness guarantee provided by the view indexes is used by the optimizer.

A análise acima foi feita com a edição expressa do SQL Server 2014. Também tentei com a edição do desenvolvedor do SQL Server 2016. A opção NOEXPAND não parece ser necessária com a edição de desenvolvimento para obter os ganhos de desempenho, mas ainda é recomendada .

Há menos de 5 meses, a Microsoft liberou as edições para desenvolvedores . A licença restringe o uso apenas para desenvolvimento, o que significa que o banco de dados não pode ser usado em um ambiente de produção. Portanto, se você está procurando testar tabelas com otimização de memória, criptografia, R etc., não tem mais a desculpa de não ter licença. Eu instalei com sucesso no meu computador há alguns dias junto com o SQL Server 2014 Express sem problemas.