Diferença entre Procurar Predicado e Predicado

Vamos lançar um milhão de linhas em uma tabela temporária junto com algumas colunas:

CREATE TABLE #174860 (
PK INT NOT NULL, 
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (PK)
);

INSERT INTO #174860 WITH (TABLOCK)
SELECT RN
, RN % 1000
, RN % 10000
FROM 
(
    SELECT TOP 1000000 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) RN
    FROM   master..spt_values v1,
           master..spt_values v2
) t;

CREATE INDEX IX_174860_IX ON #174860 (COL1) INCLUDE (COL2);

Aqui eu tenho um índice clusterizado (por padrão) na PKcoluna. Há um índice não clusterizado COL1que tem uma coluna de chave COL1e inclui COL2.

Considere a seguinte consulta:

SELECT *
FROM #174860
WHERE PK >= 15000 AND PK < 15005
AND COL2 = 5000;

Aqui não estou usando BETWEENporque Aaron Bertrand está rondando essa questão.

Como o SQL Server deve otimizar essa consulta? Bem, eu sei que o filtro em PKreduzirá o conjunto de resultados para cinco linhas. O SQL Server pode usar o índice clusterizado para pular para essas cinco linhas em vez de ler todos os milhões de linhas da tabela. No entanto, o índice clusterizado possui apenas a coluna PK como coluna-chave. Uma vez que a linha é lida na memória, precisamos aplicar o filtro em COL2. Aqui, PKé um predicado de busca e COL2é um predicado.

O SQL Server encontra cinco linhas usando o predicado de busca e reduz ainda mais essas cinco linhas para uma linha com o predicado normal.

Se eu definir o índice clusterizado de forma diferente:

CREATE TABLE #174860 (
PK INT NOT NULL, 
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (COL2, PK)
);

E execute a mesma consulta, recebo resultados diferentes:

Nesse caso, o SQL Server pode buscar usando as duas colunas na WHEREcláusula. Exatamente uma linha é lida da tabela usando as colunas-chave.

Para mais um exemplo, considere esta consulta:

SELECT *
FROM #174860
WHERE COL1 = 500
AND COL2 = 3545;

O índice IX_174860_IX é um índice abrangente porque contém todas as colunas necessárias para a consulta. No entanto, apenas COL1é uma coluna chave. O SQL Server pode procurar com essa coluna para localizar as 1.000 linhas com um COL1valor correspondente. Ele pode filtrar ainda mais essas linhas na COL2coluna para reduzir o resultado final definido para 0 linhas.