Desafio: Erros gerados em consulta indexada

  • Article

 

No desafio anterior, comentamos sobre a restrição de uso do ORDER BY dentro de uma View. Dessa vez, o desafio está relacionado a uma consulta que passa a gerar erros após a criação de índices.

Imagine uma tabela composta pelos campos (ID, Nome, Idade) como na figura abaixo e uma consulta para determinar o número de pessoas com mais de 18 anos.

image

 SELECT Menores = COUNT(*) FROM vwLista
WHERE campoNome = 'Idade' and CAST(campoValor as INT) > 18


 Menores
-----------
5

(1 row(s) affected)

Os dados foram expostos a partir de uma view, definida sobre as tabelas ListaItem, ColunaItem e DetalheItem.

 CREATE VIEW vwLista 
AS
SELECT l.listaId, c.campoNome, i.campoValor 
FROM dbo.ListaItem l 
    INNER JOIN dbo.DetalheItem i ON l.listaId = i.listaId 
    INNER JOIN dbo.ColunaItem c ON c.colId = i.colunaId

Com o objetivo de melhorar o desempenho, foram criados os seguintes índices:

 CREATE INDEX idxColId ON ColunaItem(colId)
CREATE INDEX idxColName ON ColunaItem(campoNome)
CREATE INDEX idxValor ON DetalheItem(campoValor)
CREATE INDEX idxLista ON ListaItem(listaId)
CREATE INDEX idxColNameId ON ColunaItem(campoNome,colId)

Após essas mudanças, a query começou a retornar erros.

 SELECT Menores = COUNT(*) FROM vwLista
WHERE campoNome = 'Idade' and CAST(campoValor as INT) > 18


 Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value 
'Admin' to data type int.

 

Perguntas:

1) Por que a query começou a falhar após a criação de índices?

2) Qual o problema na forma que foi escrita a query? Qual a forma correta de escrevê-la? (erro conceitual)

3) Como reescrever a View de forma a evitar esse tipo de erro?

4) Existe alguma alternativa para evitar esse tipo de erro sem alterar o código existente?

 

Escreva sua resposta nos comentários. Estou curioso para saber quais serão as soluções propostas.

 

Script para criação de tabela e dados:

    CREATE TABLE ListaItem    (listaId INT, nome VARCHAR(10), itemId INT)   CREATE TABLE ColunaItem    (colId INT, campoNome VARCHAR(10), campoTipo VARCHAR(10))   CREATE TABLE DetalheItem    (itemId INT IDENTITY(1,1),     listaId INT, colunaId INT, campoValor VARCHAR(256))   INSERT ListaItem (listaId, nome) VALUES     (1,'ADM'), (2,'USR1'), (3,'USR2'), (4,'USR3'), (5,'USR4')   INSERT ColunaItem (colId, campoTipo, campoNome) VALUES    (1,'CHAR','Nome'), (2,'INT','Idade')   INSERT DetalheItem (listaId, colunaId, campoValor) VALUES     (1,1,'Admin'), (1,2,'31'), (2,1,'User A'), (2,2,'25'),         (3,1,'User B'), (3,2,'26'), (4,1,'User C'), (4,2,'19'),         (5, 1, 'User D'), (5, 2, '21')       CREATE INDEX idxColunaId ON DetalheItem(colunaId)   GO      CREATE VIEW vwLista    AS   SELECT l.listaId, c.campoNome, i.campoValor FROM dbo.ListaItem l       INNER JOIN dbo.DetalheItem i ON l.listaId = i.listaId       INNER JOIN dbo.ColunaItem c ON c.colId = i.colunaId   GO   -- Criacao dos indices adicionais: a query para de funcionar   CREATE INDEX idxColId ON ColunaItem(colId)   CREATE INDEX idxColName ON ColunaItem(campoNome)   CREATE INDEX idxValor ON DetalheItem(campoValor)   CREATE INDEX idxLista ON ListaItem(listaId)   CREATE INDEX idxColNameId ON ColunaItem(campoNome, colId)

 

desafio.sql