Fala aee pessoal, espero que estejam todos ótimos !!
No capítulo anterior, criamos um índice NonClustered (“criamos o que Ithalo???) acho bom dar uma voltada aqui https://ithalobruno.tech/?p=63 .
Usamos ele na pesquisa pelo nome da pessoa, porém, conteúdo e, entretanto, ele não foi utilizado. E aí bateu aquele desespero né?? Por que criamos o índice feliz e contente, e o SQL não o usou? O SQL não gosta de mim? Eu fiz o índice errado?? Calma, calma, calma.
Vamos dar uma relembrada aqui, o índice clustered é exatamente a cópia da sua tabela, ordenada pela chave que você definiu na criação do mesmo. Já o índice NonClustered é “apenas a organização” da sua tabela pelo CAMPO, que você definiu na criação do seu índice.
Pois bem, uma vez que você usar o índice clustered o SQL, já tem todas as informações que precisa para realizar a pesquisa, e o retorno do seu SELECT.
Quando usamos o índice nonclustered, para realizar a pesquisa, e o usamos o maldito * no SELECT o que acontece?
Bom, vai depender muito do cenário, no exemplo que usamos da tabela PESSOA, temos apenas 4 registros, e, portanto, o SQL faz um SCAN em toda a tabela. Ele faz o SCAN justamente porquê usamos o “*”. Como assim Ithalo??
Nós criamos o índice atoa?
– Depende, se a gente usar o * no Select, SIM, nós nunca vamos usar nosso índice. Porém o que acontece, quando, ao invés do *, nós retornamos apenas o nome da pessoa?
Vamos praticar!
Vou presumir que você já executou estes scripts, ok amiguinhos?
CREATE TABLE Pessoa(
CODIGO INT IDENTITY(1,1) NOT NULL,
NOME VARCHAR(50) NOT NULL,
DATA_NASCIMENTO DATE NOT NULL,
SEXO CHAR NOT NULL,
ALTURA INT
)
CREATE CLUSTERED INDEX IX_Codigo
ON Pessoa (Codigo);
GO
USE [Estudos]
GO
INSERT INTO [dbo].[Pessoa]
([NOME]
,[DATA_NASCIMENTO]
,[SEXO]
,[ALTURA])
VALUES
('JOAO',
'1980-01-01',
'M',
180)
GO
INSERT INTO [dbo].[Pessoa]
([NOME]
,[DATA_NASCIMENTO]
,[SEXO]
,[ALTURA])
VALUES
('MARIA',
'1986-02-15',
'F',
170)
GO
INSERT INTO [dbo].[Pessoa]
([NOME]
,[DATA_NASCIMENTO]
,[SEXO]
,[ALTURA])
VALUES
('CARLOS',
'1990-03-31',
'M',
165)
GO
INSERT INTO [dbo].[Pessoa]
([NOME]
,[DATA_NASCIMENTO]
,[SEXO]
,[ALTURA])
VALUES
('ROBERTA',
'1986-08-25',
'F',
181)
GO
No post anterior, nós executamos o maldito SELECT * FROM, e o índice não foi usado.
Vamos trocar o * pelo NOME? (Lembrem de olhar o Execution Plan CTRL + M)
Select Nome from Pessoa
Where Nome = 'Joao'

Aeeeeeeeeeeee, coroiiii , agora sim, ele está usando o índice que criamos e ainda fazendo o SEEK!!
Vamos entender!
Um índice NonClustered é composto por 2 informações, a chave do índice e a chave do índice clustered. Como assim Ithalo?
Quando criamos o índice nonClustered para nossa tabela Pessoa, usamos a coluna NOME para ser nossa coluna ordenada desse índice.
Quando nós executamos o SELECT *, o que estamos esperando de retorno dessa pesquisa?
Todos os campos da tabela correto? No caso da tabela pessoa que criamos, estamos esperando Codigo – Nome – Data_Nascimento – Sexo e a Altura da pessoa. Correto?
E aí você quer que o SQL se vire com esse “mundaréu” de campo aí, usando apenas o índice que o bonitão(ona) criou somente com Nome?
Ou seja, ele não vai usar nosso índice, pois os campos Codigo, Data_Nascimento, Sexo e Altura, não fazem parte do índice. E por isso ele faz o SCAN (no índice clustered) é muito menos custoso para ele.
Mas, e o que aconteceria se a tabela tivesse trocentos milhões de registros, ele faria o SCAN também?
Vamos brincar!
Primeiramente vamos criar uma tabela , criar nossos índices e inserir registros nesta tabela.
CREATE TABLE TABELASCAN(
CODIGO INT PRIMARY KEY IDENTITY(1,1),
NOME VARCHAR(120),
DATAATUALIZACAO DATETIME
)
CREATE NONCLUSTERED INDEX IX_Nome
ON TABELASCAN (Nome);
GO
DECLARE @CONTADOR int = 1
BEGIN
WHILE @CONTADOR <= 20000
BEGIN
INSERT INTO TABELASCAN(NOME,DATAATUALIZACAO)
VALUES(
CONCAT('NOME',@CONTADOR),GETDATE()
)
SET @CONTADOR = @CONTADOR + 1;
END;
END;
Agora vamos brincar com as possibilidades que conhecemos.
Primeiro vamos usar o índice clustered, (mas Ithalo, nos scripts, não tem a criação dele, perceba que quando criamos a tabela, já definimos que temos uma PK, e o SQL já entende que ela é nosso índice clustered, você pode dropar e criar outro caso seja interessante.)
SELECT * FROM TABELASCAN
WHERE CODIGO = 18997

Agora vamos usar o índice nonClustered.
SELECT NOME FROM TABELASCAN
WHERE NOME = 'NOME18997'

E se eu usar o * ali o que acontece? Nós já aprendemos que em uma tabela com poucos registros, o SQL faz o SCAN, e agora em uma tabela com 20mil registros, o que será que ele faz?
SELECT * FROM TABELASCAN
WHERE NOME = 'NOME18997'
Podemos perceber que apareceu um operador novo no Execution Plain. E aqui entra a parte mais legal deste post.

Key Lookup: Sabemos que o índice clustered possui todos os campos da nossa tabela, e o índice nonclustered possui o campo que definimos + o índice clustered.
No exemplo que estamos trabalhando, criamos o índice baseado no campo nome.
Quando pesquisamos os registros, ele usa o índice para filtrar, mas no retorno ele procura o campo dataatualizacao, e esta não está no índice, como ele resolve esta situação?
Usando exatamente o índice clustered, que compõe a chave do índice nonClustered.

Espero que tenham gostado.
Um abraço!!
E até a próxima!!!