Melhorias no SQL Server 2014 – Criação de Índices em Tabelas Temporárias e Variáveis Tipo Tabela

Posted on julho 3, 2013

3


 

Diversos recursos foram implementados na nova versão do SQL Server, um novo e interessante recurso é a possbilidade de se criar índices em tabelas temporárias (nova hint para criação) e em variáveis tipo tabela. Tentando trazer para um cenário real, imagine que você está armazenando uma quantidade considerável de registros em um armazenamento temporário (tabela temporária ou variável tipo tabela) o problema é que independente da quantidade de registros inseridos o Q.O no SQL Server 2012 somente realizava um table scan nesses tipos de tabela, agora no SQL Server 2014 é possível realizar um index seek.

Correção:  A possibilidade da realização de um SEEK em uma tabela temporária é possível desde o SQL Server 7.0. Porém a HINT é um novo recurso.

 

USE Demos

go

 

               DROP TABLE #DadosClientesWithIndex

               DROP TABLE #DadosClientesNoIndex

 

 

CREATE TABLE #DadosClientesWithIndex 

(

ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),

Nome VARCHAR(100) NOT NULL INDEX idx_nome,

IDGUID UNIQUEIDENTIFIER NOT NULL INDEX idx_guid,  

Tipo VARCHAR(50) NOT NULL

)

 

CREATE TABLE #DadosClientesNoIndex 

(

ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),

Nome VARCHAR(100) NOT NULL,

IDGUID UNIQUEIDENTIFIER NOT NULL, 

Tipo VARCHAR(50) NOT NULL

)

SET NOCOUNT ON;

 

INSERT INTO #DadosClientesWithIndex(Nome, IDGUID, Tipo)

SELECT S.name, NEWID(), S.type_desc

FROM master.sys.objects AS S

CROSS APPLY msdb.sys.objects AS O

 

INSERT INTO #DadosClientesNoIndex(Nome, IDGUID, Tipo)

SELECT S.name, NEWID(), S.type_desc

FROM master.sys.objects AS S

CROSS APPLY msdb.sys.objects AS O

 

SELECT ID, Nome

FROM #DadosClientesWithIndex

WHERE nome = ‘sysmultiobjrefs’

GO

SELECT ID, Nome

FROM #DadosClientesNoIndex

WHERE nome = ‘sysmultiobjrefs’

Nessa demo estou criando duas tabelas, uma com o nome de #DadosClientesWithIndex que é uma tabela que no campo nome possui um índice criado e a de nome #DadosClientesNoIndex é a que não possui nenhum índice criado,  porém se perceberem, esta tabela é uma tabela temporária ou seja, se tentarmos realizar essa mesma criação no SQL Server 2012 teremos o seguinte erro:

Msg 1018, Level 15, State 1, Line 11
Incorrect syntax near ‘INDEX’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Msg 208, Level 16, State 0, Line 41
Invalid object name ‘#DadosClientesNoIndex’.

 A especificação INDEX idx_nome dessa cláusula somente é possível no SQL Server 2014, como dito anteriormente conseguimos criar índices nesse tipo de tabela, com isso se olharmos o plano de execução, agora não realizamos somente scan mais sim seek e com isso nossa resposta a consulta se torna muito mais efeciente.

Capture

 

Com isso possúimos ainda mais a possibilidade de trabalhar com tabelas temporárias de uma forma mais efetiva, um outro exemplo é a criação de uma variável tipo tabela.

 

 

DECLARE @DadosClientesWithIndex TABLE  

 

(

 

ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),

 

Nome VARCHAR(100) NOT NULL INDEX idx_nome,

 

IDGUID UNIQUEIDENTIFIER NOT NULL INDEX idx_guid,  

 

Tipo VARCHAR(50) NOT NULL

 

)

 

 

DECLARE @DadosClientesNoIndex TABLE

 

(

 

ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),

 

Nome VARCHAR(100) NOT NULL,

 

IDGUID UNIQUEIDENTIFIER NOT NULL, 

 

Tipo VARCHAR(50) NOT NULL

 

)

 

 

 

 

SET NOCOUNT ON;

 

 

INSERT INTO @DadosClientesWithIndex(Nome, IDGUID, Tipo)

 

SELECT S.name, NEWID(), S.type_desc

 

FROM master.sys.objects AS S

 

CROSS APPLY msdb.sys.objects AS O

 

 

INSERT INTO @DadosClientesNoIndex(Nome, IDGUID, Tipo)

 

SELECT S.name, NEWID(), S.type_desc

 

FROM master.sys.objects AS S

 

CROSS APPLY msdb.sys.objects AS O