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!!!