Como fazer com que as exibições indexadas funcionem para SQLCLR?

As visualizações indexadas podem fazer alguma coisa ? Nunca ?

Na verdade, é meio miserável quando você pensa em uma exibição indexada como uma solução, apenas para descobrir que seu caso de uso atinge uma das muitas limitações das exibições indexadas (estou olhando para você, LEFT JOIN).

Você está certo de que não pode incluir o SQLCLR nas chaves de índice da exibição indexada. No entanto, você pode incluí-lo na SELECTlista da definição de exibição, que persistirá o valor no disco. Assim, você pode pelo menos evitar o custo de calcular o valor em tempo real ao ler a tabela.

No banco de dados de exemplo AdventureWorks2014, criei esta exibição na Person.Persontabela com um nome encantador:

CREATE OR ALTER VIEW dbo.PersonWithHashForSomeReason
WITH SCHEMABINDING
AS
SELECT 
    BusinessEntityID,
    PersonType,
    dbo.SpookyHash(CONVERT(binary(50), FirstName)) AS FirstNameHash
FROM Person.Person
GO

Nota: eu estava com preguiça de escrever minha própria função CLR, então esta é deste Q&A .

Eu posso tornar isso uma exibição indexada agrupando-a em BusinessEntityID:

CREATE UNIQUE CLUSTERED INDEX CX_BusinessEntityID 
ON dbo.PersonWithHashForSomeReason (BusinessEntityID);
GO

A procura de um conjunto específico de linhas na tabela produz uma verificação de índice ( link do plano de execução ). Observe a falta de um Compute Scalaroperador, que normalmente seria usado para produzir o valor de hash. Como o hash é mantido em disco na exibição indexada, isso não é necessário:

SELECT BusinessEntityID, FirstNameHash 
FROM dbo.PersonWithHashForSomeReason WITH (NOEXPAND)
WHERE FirstNameHash = 0x910C426C533F2C0AAF350158331E3B01;

Eu tive que usar uma NOEXPANDdica para fazê-lo usar a visualização.

Você notará que os avisos no plano se devem ao fato de que toda a tabela foi varrida para encontrar esses valores, já que não há nenhum índice digitado no FirstNameHash.

Infelizmente, a tentativa de criar um índice não clusterizado nesse valor persistente falha:

CREATE NONCLUSTERED INDEX IX_FirstNameHash 
ON dbo.PersonWithHashForSomeReason (FirstNameHash);

Msg 1976, Level 16, State 1, Line 48
Não é possível criar índice ou estatísticas 'IX_FirstNameHash' na exibição 'dbo.PersonWithHashForSomeReason' porque não é possível verificar se a coluna de chave 'FirstNameHash' é precisa e determinística. Considere remover a coluna da chave de índice ou estatística, marcando a coluna persistida na tabela base se ela for computada ou usando uma coluna não derivada de CLR na chave.

Isso ocorre porque o SQL Server não confia em nós :

Por que não confiamos no usuário? O que realmente pode dar errado aqui??

Suponha que o usuário defina incorretamente o atributo personalizado IsDeterministic como true para uma função não determinística e suponha que ele seja capaz de criar um índice em uma coluna computada que invocou essa função sem persisti-la. [...] Isso pode levar à corrupção do índice , pois a função pode retornar valores diferentes para a mesma entrada, pois a função não é determinística.

Portanto, para a segurança do usuário, o Sql Server nesta versão REQUER que o usuário persista as colunas computadas (diferentemente do caso tsql) para realmente indexar as colunas computadas.

Imagino que a mesma limitação exista para índices de texto completo, embora eu não tenha tentado.

A conclusão de tudo isso, como você mencionou em seu próprio post, é que você está meio preso aqui. Realmente sua única opção é:

• aceite a varredura da tabela (não é ideal e provavelmente nem prática com base em sua descrição) ou
• use gatilhos para "manualmente" manter esse valor atualizado em uma coluna normal

Portanto, preciso de um índice de texto completo em uma coluna calculada persistente ou em uma exibição materializada.

Um índice de texto completo não pode ser criado em uma coluna não determinística.

Esse erro foi produzido ao tentar indexar em texto completo a Searchcoluna definida em uma exibição indexada conforme abaixo:

CREATE TABLE dbo.T1 (id integer PRIMARY KEY, v1 datetime2(3) NULL);
CREATE TABLE dbo.T2 (id integer PRIMARY KEY, v2 decimal (9, 2) NULL);
CREATE TABLE dbo.T3 (id integer PRIMARY KEY, v3 money NULL);
GO
INSERT dbo.T1 (id, v1) VALUES (1, SYSUTCDATETIME());
INSERT dbo.T2 (id, v2) VALUES (1, 1234567.89);
INSERT dbo.T3 (id, v3) VALUES (1, $987.65);
GO
CREATE VIEW dbo.FormattedData
WITH SCHEMABINDING
AS
SELECT 
    T1.id,
    Search =
        N'Datetime: ' + SQL#.Date_Format(T1.v1, N'D', N'de-de') + N'; ' +
        N'Decimal: ' + SQL#.Math_FormatDecimal(T2.v2, N'###,###,###.00', N'') + N'; ' +
        N'Money: ' + SQL#.Math_FormatDecimal(T3.v3, N'C', N'de-de')
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.id = T1.id
JOIN dbo.T3 AS T3
    ON T3.id = T2.id;

As funções referenciadas lá são da biblioteca SQL# CLR e estão disponíveis na versão gratuita. Eles correspondem à função da FORMATfunção, o que não é permitido em uma exibição indexada.

No entanto, parece improvável que a pesquisa de texto completo seja o que você precisa. Não é adequado para pesquisas com curingas à esquerda ou correspondência geral de strings onde não-palavras estão envolvidas. Como diz a documentação :

As consultas de texto completo realizam pesquisas linguísticas em dados de texto em índices de texto completo operando em palavras e frases com base nas regras de um idioma específico, como inglês ou japonês. As consultas de texto completo podem incluir palavras e frases simples ou várias formas de uma palavra ou frase.

Se você precisar de pesquisas rápidas com curinga e/ou sem idioma, e as funções internas do T-SQL não funcionarem bem o suficiente após o teste, uma alternativa é usar n-grams, como descrevo em Trigram Wildcard String Search em Servidor SQL .

Esse artigo contém uma implementação completa, incluindo gatilhos simples para manter uma fonte de dados (por exemplo, uma exibição indexada como acima) sincronizada com os n-grams o tempo todo, se necessário. Se você não precisar de sincronização ao vivo, poderá descobrir que reconstruir os n-grams completamente de vez em quando é bom o suficiente para seus usuários, pois os gatilhos não são necessários (por mais leves que sejam).