Duvida Indice

Question

Qual a diferenca de usar ou nao INCLUDE em um indice. No exemplo abaixo, eu poderia adicionar todos os campos sem INCLUDE, mas o plano de execucao sugerio criar esse indice. So queria mesmo saber se tem direferença:

CREATE NONCLUSTERED INDEX [IX_MAV_Devolucao_Composto_1] ON [dbo].[MAV_Devolucao] 
(
	[Bonificacao] ASC,
	[Status] ASC,
	[Docto] ASC,
	[NF_DtEmissao] ASC
)
INCLUDE ( [Devolucao],
[Origem],
[CadCli],
[Cliente],
[NF_DtSaidaEntrada],
[FretePagCli],
[NaturezaOperacao],
[CadTrans],
[Trans],
[Veiculo],
[CompIE],
[CompEspecie],
[CompMarca],
[CompQtde],
[CompNumero],
[CompPesoB],
[CompPesoL],
[CompObsCorpo]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Answer 1

Olá DaviSaba,

Acho que a Thread abaixo pode lhe ser útil.

http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/5819cef3-db5d-4544-a90c-6b616fe6027b/#f24d6b74-f1db-40c3-86d1-cf42e149d63a

---

David Silva | MCITP| MCTS | MCP | ITILF | Blog: http://tilive.wordpress.com

Answer 2

Davi,

A utilização do Include ou não é uma questão interessante sobre os indices, mas de forma rapida, posso explicar da seguinte maneira:

Voce possui em seu indice um campo chave, esse campo chave aparecera nos nós do seu indice, e a estrutura de dados arvore sera dividida por este campo.

Quando voce possui apenas um indice com o campo chave, tudo que voce possui nos nós folhas é o numero das linhas aonde tenha tal valor.

Fazendo com que seja necessario ir até a tabela para buscar outros dados alem do chave (No plano de execução voce vera isso como um look up).

Um exemplo de uma busca apenas pelo campo chave é:

SELECT Id

FROM Tabela

WHERE Id = 5

Neste caso, sua unica pesquisa sera no indice, trazendo o valor em si. Se voce alterar a query para:

SELECT Id, Nome

FROM Tabela

WHERE Id = 5

Ao encontrar os ID 5 na estrutura do indice, voce devera fazer uma pesquisa em sua tabela, nos numeros da linha apontados no indice, para buscar a informação do nome, mantendo o seguinte fluxo:

Inicia -> Indice -> Tabela -> Retorno.

Agora, se colocarmos o INCLUDE (Nome) em nosso indice, e executarmos o ultimo select, o indice sera:

Inicia -> Indice -> Retorno.

Pois apesar da estrutura de arvore do indice estar divida pelo campo chave (Id), o campo nome sera carregado por toda estrutura, não sendo necessario ir buscar este dado na tabela uma vez que voce ja o possui no indice.

Tome cuidado pois esses indices são chamados de FullQueryIndex, ou seja, preencher 100% uma query completa, e realmente para tal query talvez seja a melhor opção, porem, estamos falando de uma query Ad-Hoc, o que talvez não compense a criação do mesmo, ainda mais por que pode interferir em seu ambiente transacional.

A sugestão do SQL Server é valida e muitas vezes voce a utilizara, porem tome cuidado, não estou falando para criar ou não o indice, e sim que talvez, se voce for executar essa query apenas uma vez, não acho que valha o esforço, porem, se for uma query recorrente de seu ambiente transacional, talvez seja interessante, mas novamente, é preciso analisar.

Como dica: Crie o indice, execute a query, veja o plano, veja se houve efetivamente uma melhora ou não.

---

Fabrizzio A. Caputo
MCT
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Blog Empresa: www.tripletech.com.br/blog
Twitter: @FabrizzioCaputo
Email: [e-mail]

Answer 3

Obrigado pelas explicacoes. Entendi perfeitamente a explicacao do SELECT ID, Nome ... com e sem o INCLUDE.

Agora eu posso cricar o INDICE de duas formas:

CREATE NONCLUSTERED INDEX [IX_Texte] ON [dbo].[TabelaCliente] 
(
	[Codigo] ASC,
	[Nome] ASC
)
OU
CREATE NONCLUSTERED INDEX [IX_Teste] ON [dbo].[TabelaCliente] 
(
	[codigo] ASC
)
INCLUDE ( [Nome])
Existe diferenca entre essas duas formas?

Answer 4

DaviSaba,

No final segue um script bem detalhado com todos os casos
possiveis que consegui pensar.

Porem, sim, quando voce colocar o campo no include, um
filtro por este campo fara a utilização do indice, porem seu custo (Vide plano
de execução) sera maior do que colocando-o como indice chave (Primeiro caso),
quando o mesmo for a segunda coluna (No exemplo...), porem, não se for a
primeira. Segue abaixo.

OBS: Tudo depende de seus dados e da quantidade, é
impossivel dizer 100% se ele vai ou não utilizar tal indice, a melhor opção
continua sendo criar e testar.

--CRIA TABELA

CREATE TABLE TesteDavi

(

      CodigoINT,

      ClienteINT

)

--CARREGA DADOS
NA TABELA

DECLARE @Cont INT = 0

DECLARE @Sql VARCHAR(8000)

SET @Sql = ''

WHILE(@Cont < 1000000)

BEGIN

      SET@Sql='INSERT INTO TesteDavi
VALUES ('+CAST(@ContASVARCHAR(100))+','+CAST(@Cont+100 ASVARCHAR(100))+')'

      EXEC
(@Sql)

      SET@Cont
=@Cont+1

END

-----PRIMEIRO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

--FULLTABLE SCAN

-----CRIACAO DE INDICE 1

CREATE INDEX IX_01 ON TesteDavi(Codigo) WITH (SORT_IN_TEMPDB=ON)

-----SEGUNDO SELECT

SELECT Codigo

FROM TesteDavi

WHERE Codigo = 500000

--INDEX SCAN

-----TERCEIRO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 500000

--INDEX SCAN + LookUp (Comentado acima)

-----QUARTO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Cliente = 5

--FULLTABLE SCAN

-----CRIACAO DE INDICE 2

CREATE INDEX IX_01 ON TesteDavi(Codigo, Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

-----QUINTO SELECT

SELECT Codigo

FROM TesteDavi

WHERE Codigo = 500000

--INDEX SCAN

-----SEXTO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 500000

--INDEX SCAN SEM LOOKUP

-----SETIMO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Cliente = 5--3,11736

--INDEX SCAN

-----CRIACAO DE
INDICE 3

CREATE INDEX IX_01 ON TesteDavi(Codigo) INCLUDE(Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

-----OITAVO SELECT

SELECT Codigo

FROM TesteDavi

WHERE Codigo = 500000

--INDEX SCAN

-----NONO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 500000

--INDEX SCAN SEM LOOKUP

-----DECIMO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Cliente = 5--3,11736

--INDEX SCAN

-----TESTE GERAIS

CREATE INDEX IX_01 ON TesteDavi(Cliente) INCLUDE(Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

-----DECIMO
PRIMEIRO SELECT

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Cliente = 5--0,0032831

-----CRIACAO DE
INDICE 4

CREATE INDEX IX_01 ON TesteDavi(Codigo, Cliente) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

-----

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Cliente = 5--3,11736

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 5--0,0032831

-----CRIACAO DE INDICE 4

CREATE INDEX IX_01 ON TesteDavi(Cliente, Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

-----

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Cliente = 5--0,0032831

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 5--3,11736

-----CRIACAO DE INDICE 4

CREATE INDEX IX_01 ON TesteDavi(Cliente, Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 5--3,11736

CREATE INDEX IX_01 ON TesteDavi(Cliente)INCLUDE(Codigo) WITH (SORT_IN_TEMPDB=ON, DROP_EXISTING=ON)

SELECT Codigo, Cliente

FROM TesteDavi

WHERE Codigo = 5--3,11736

---

Fabrizzio A. Caputo
MCT
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Blog Empresa: www.tripletech.com.br/blog
Twitter: @FabrizzioCaputo
Email: [e-mail]