Estou tentando entender por que usar uma variável de tabela está impedindo o otimizador de usar uma busca de índice e, em seguida, marcar uma pesquisa em vez de uma varredura de índice.
Preenchendo a tabela:
CREATE TABLE dbo.Test
(
RowKey INT NOT NULL PRIMARY KEY,
SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
ForeignKey INT NOT NULL
)
INSERT dbo.Test
(
RowKey,
ForeignKey
)
SELECT TOP 1000000
ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
ABS(CHECKSUM(NEWID()) % 10)
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey)
Preencha uma variável de tabela com um único registro e tente pesquisar a chave primária e a segunda coluna pesquisando na coluna da chave estrangeira:
DECLARE @Keys TABLE (RowKey INT NOT NULL)
INSERT @Keys (RowKey) VALUES (10)
SELECT
t.RowKey,
t.SecondColumn
FROM
dbo.Test t
INNER JOIN
@Keys k
ON
t.ForeignKey = k.RowKey
Segue abaixo o plano de execução:
Agora, a mesma consulta usando uma tabela temporária:
CREATE TABLE #Keys (RowKey INT NOT NULL)
INSERT #Keys (RowKey) VALUES (10)
SELECT
t.RowKey,
t.SecondColumn
FROM
dbo.Test t
INNER JOIN
#Keys k
ON
t.ForeignKey = k.RowKey
Este plano de consulta usa uma pesquisa de busca e marcador:
Por que o otimizador deseja fazer a pesquisa de favoritos com a tabela temporária, mas não com a variável da tabela?
A variável de tabela é usada neste exemplo para representar dados provenientes de um tipo de tabela definido pelo usuário em um procedimento armazenado.
Percebo que a busca de índice pode não ser apropriada se o valor da chave estrangeira ocorrer centenas de milhares de vezes. Nesse caso, uma varredura provavelmente seria uma escolha melhor. Para o cenário que criei, não havia nenhuma linha com valor 10. Ainda acho o comportamento interessante e gostaria de saber se há uma razão para isso.
Adicionar OPTION (RECOMPILE)não alterou o comportamento. O UDDT tem uma chave primária.
@@VERSIONé SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hipervisor)
