Fala #galera,

Vou iniciar uma pequena série de posts para falar sobre Tabelas Temporárias.

Nesse primeiro post vou falar sobre Tabelas Temporárias Locais. #gogogo

Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais

Dicas T-SQL – Tabelas Temporárias – Parte 2 – Tabelas Globais

Dicas T-SQL – Tabelas Temporárias – Parte 3 – Tabelas Variáveis

Tabelas Temporárias:

Em alguns momentos, nós precisamos armazenar alguns dados apenas de forma temporária ou que seja visível apenas para a sessão atual. Nesses casos, podemos utilizar as tabelas temporárias ao invés das tabelas convencionais ou tabelas permanentes.

Segue abaixo alguns cenários úteis:

Ao fazer um loop / cursor, podemos utilizar as tabelas temporárias para armazenar os resultados dos processamentos de cada iteração;
Se você precisa fazer uma determinada consulta várias vezes para utilizar o mesmo resultado (por exemplo, as vendas do mês atual) e alterar apenas alguns filtros depois, você poderia salvar esse resultado em uma tabela temporária e depois fazer os filtros nessa nova tabela. Se essa consulta for demorada (alguns segundos ou minutos), podemos ter também um ganho de performance, pois iremos executar ela apenas uma vez e depois o resultado vai estar salvo na tabela temporária (mais rápida).
Em alguns Casos de Tuning, eu particularmente gosto de fazer um filtro em uma consulta utilizando menos colunas no SELECT e guardando o ID da PRIMARY KEY (UNIQUE) em uma tabela temporária para depois fazer um JOIN com a tabela original e buscar as outras colunas, pois dessa forma eu consigo utilizar um índice de uma forma mais eficiente (index seek). No exemplo abaixo, a query original retorna 50 colunas no SELECT e eu só precisava da coluna da data para fazer o filtro no índice. Com isso, eu vou fazer primeiro o filtro por data e salvar em uma tabela temporária (aqui só precisei das colunas Id_Venda e Dt_Venda) e depois faço um JOIN com a tabela original pelo Id_Venda que é UNIQUE. Essa Técnica de Tuning é muito útil no meu dia a dia e já perdi as contas de quantos casos já resolvi dessa forma e acho bem simples também. =)

-- ANTES
SELECT Col1, Col2, Col3, ..., Col50
FROM Vendas
WHERE 
	Dt_Venda >= '20200701'
	AND Dt_Venda < '20200801'

-- DEPOIS
-- SELECIONANDO OS IDS DAS VENDAS - FAZ UM "INDEX SEEK" NO INDICE POR DATA (MAIS RAPIDO)
IF(OBJECT_ID('tempdb..#TEMP_VENDAS') IS NOT NULL)
	DROP TABLE #TEMP_VENDAS

SELECT Id_Venda
INTO #TEMP_VENDAS
FROM Vendas
WHERE 
	Dt_Venda >= '20200701'
	AND Dt_Venda < '20200801'

SELECT Col1, Col2, Col3, ..., Col50
FROM Vendas V
JOIN #TEMP_VENDAS T ON V.Id_venda = T.Id_Venda

-- ANTES

SELECT Col1, Col2, Col3, ..., Col50

FROM Vendas

WHERE

Dt_Venda >= '20200701'

AND Dt_Venda < '20200801'

-- DEPOIS

-- SELECIONANDO OS IDS DAS VENDAS - FAZ UM "INDEX SEEK" NO INDICE POR DATA (MAIS RAPIDO)

IF(OBJECT_ID('tempdb..#TEMP_VENDAS') IS NOT NULL)

DROP TABLE #TEMP_VENDAS

SELECT Id_Venda

INTO #TEMP_VENDAS

FROM Vendas

WHERE

Dt_Venda >= '20200701'

AND Dt_Venda < '20200801'

SELECT Col1, Col2, Col3, ..., Col50

FROM Vendas V

JOIN #TEMP_VENDAS T ON V.Id_venda = T.Id_Venda

Um outro exemplo seria utilizar tabelas temporárias quando o usuário não tiver permissão para criar tabelas permanentes na database;
Entre outros… =)

Observação Importante:

As Tabelas Temporárias são diferentes das Tabelas Temporais (foi introduzida no SQL Server 2016). São conceitos distintos e nesse momento não vou falar sobre Tabelas Temporais OK.

No SQL Server, podemos utilizar três tipos de tabelas temporárias:

Tabelas temporárias locais;
Tabelas temporárias globais;
Tabelas variáveis.

Vou abordar cada um desses tipos de tabelas em um post diferente.

Todos esses três tipos irão criar as tabelas temporárias na database “tempdb”. Portanto, tome cuidado com a utilização das tabelas temporárias, pois se elas forem muito grandes, podemos ter um crescimento da database “tempdb”. Por isso é importante monitorar também o tamanho e crescimento dessa database. Além disso, fique de olho na quantidade de requisições nessa database, pois podemos ter um gargalo se ela estiver sendo muito utilizada. #ficaadica

Agora vamos iniciar falando sobre as Tabelas Temporárias Locais! #gogogo

Tabelas Temporárias Locais

“Mas Luiz, como eu faço pra criar uma Tabela Temporária Local? É muito difícil?”

Pelo contrário, é muito simples! É a mesma sintaxe de criação de uma tabela permanente e a diferença será apenas no nome da tabela que irá iniciar sempre com o prefixo “#”.

Segue abaixo uma comparação da criação das tabelas:

USE Traces

-- TABELA PERMANENTE
CREATE TABLE TABELA_TESTE_PERMANENTE (
	Id_Teste INT IDENTITY(1,1),
	Nome VARCHAR(100)
)

-- TABELA TEMPORÁRIA
CREATE TABLE #TABELA_TESTE_TEMPORARIA (
	Id_Teste INT IDENTITY(1,1),
	Nome VARCHAR(100)
)

USE Traces

-- TABELA PERMANENTE

CREATE TABLE TABELA_TESTE_PERMANENTE (

Id_Teste INT IDENTITY(1,1),

Nome VARCHAR(100)

)

-- TABELA TEMPORÁRIA

CREATE TABLE #TABELA_TESTE_TEMPORARIA (

Id_Teste INT IDENTITY(1,1),

Nome VARCHAR(100)

)

Agora vamos inserir alguns dados e fazer um SELECT nas tabelas.

-- INSERINDO DADOS NAS TABELAS
INSERT INTO TABELA_TESTE_PERMANENTE
VALUES('TESTE 1 - TABELA PERMANENTE'),('TESTE 2 - TABELA PERMANENTE')

INSERT INTO #TABELA_TESTE_TEMPORARIA
VALUES('TESTE 1 - TABELA TEMPORÁRIA'),('TESTE 2 - TABELA TEMPORÁRIA')

SELECT * FROM TABELA_TESTE_PERMANENTE

SELECT * FROM #TABELA_TESTE_TEMPORARIA

-- INSERINDO DADOS NAS TABELAS

INSERT INTO TABELA_TESTE_PERMANENTE

VALUES('TESTE 1 - TABELA PERMANENTE'),('TESTE 2 - TABELA PERMANENTE')

INSERT INTO #TABELA_TESTE_TEMPORARIA

VALUES('TESTE 1 - TABELA TEMPORÁRIA'),('TESTE 2 - TABELA TEMPORÁRIA')

SELECT * FROM TABELA_TESTE_PERMANENTE

SELECT * FROM #TABELA_TESTE_TEMPORARIA

Repare que você vai utilizar a tabela temporária normalmente como se estivesse operando com uma tabela permanente. A sintaxe será a mesma também no SELECT, INSERT, UPDATE e DELETE. =)

Lembra que eu falei que as tabelas temporárias são criadas na database “tempdb”? Agora repare que no SELECT nós não precisamos especificar o nome da database antes da tabela temporária.

O curioso é que mesmo especificando as databases, o resultado será o mesmo, pois o SQL Server identifica que se trata de uma tabela temporária. =)

-- ESPECIFICANDO AS DATABASES
SELECT * FROM tempdb..#TABELA_TESTE_TEMPORARIA

SELECT * FROM Traces..#TABELA_TESTE_TEMPORARIA

-- ESPECIFICANDO AS DATABASES

SELECT * FROM tempdb..#TABELA_TESTE_TEMPORARIA

SELECT * FROM Traces..#TABELA_TESTE_TEMPORARIA

Definição e Nomenclatura da Tabela Temporária:

Se você quiser ver a definição da tabela temporária, você pode utilizar a procedure “sp_help” ou o atalho ALT + F1. Segue um post como referência:

https://luizlima.net/dicas-ssms-retornando-informacoes-sobre-uma-tabela/

Contudo, se você executar em uma database diferente da “tempdb” será gerado um erro informando que a tabela não existe!

USE Traces

EXEC sp_help '#TABELA_TESTE_TEMPORARIA'

USE Traces

EXEC sp_help '#TABELA_TESTE_TEMPORARIA'

Msg 15009, Level 16, State 1, Procedure sp_help, Line 79 [Batch Start Line 31]

The object ‘#TABELA_TESTE_TEMPORARIA’ does not exist in database ‘Traces’ or is invalid for this operation.

Portanto, lembre-se de estar conectado na database “tempdb” antes de executar os comandos.

USE tempdb

EXEC sp_help '#TABELA_TESTE_TEMPORARIA'

USE tempdb

EXEC sp_help '#TABELA_TESTE_TEMPORARIA'

“Uai Luiz, mas porque o nome da tabela temporária está diferente no resultado??? Que bruxaria é essa cara???”

Por baixo dos panos, o SQL Server precisa gerar um nome único para a tabela temporária (ele adiciona um sufixo ao nome da tabela), pois ela deve ser única para cada sessão. Mas você não precisa se preocupar com isso!

Aqui vou mostrar que é possível fazer o SELECT utilizando os dois nomes, mas quero que finja que você não sabe disso depois OK. Recomendo fortemente que utilize apenas com o nome normal da tabela temporária beleza!

USE tempdb

select * from #TABELA_TESTE_TEMPORARIA

select * from #TABELA_TESTE_TEMPORARIA____________________________________________________________________________________________000000000026

USE tempdb

select * from #TABELA_TESTE_TEMPORARIA

select * from #TABELA_TESTE_TEMPORARIA____________________________________________________________________________________________000000000026

Contudo, se você executar esse script em uma nova sessão, será gerado o erro abaixo (conforme era esperado!):

Msg 208, Level 16, State 0, Line 6

Invalid object name ‘#TABELA_TESTE_TEMPORARIA’.

Msg 208, Level 16, State 0, Line 11

Invalid object name ‘#TABELA_TESTE_TEMPORARIA____________________________________________________________________________________________000000000026’.

Escopo de uma Tabela Temporária Local:

O escopo de uma Tabela Temporária Local é apenas dentro da sessão em que ela for criada e também nos níveis posteriores da CALL STACK (procedures, triggers e batches dinâmicos) que forem chamados a partir dessa sessão.

Uma tabela temporária local é destruída automaticamente pelo SQL Server quando o seu nível de criação for removido da CALL STACK, pois ela não terá mais escopo para ser executada.

Por exemplo, se você criar uma tabela temporária em uma sessão, ela só será excluída se você fizer um DROP TABLE explicitamente ou quando essa sessão for finalizada.

Vamos ver alguns exemplos para fixar esses conceitos. xD

Exemplo 1:

Nesse exemplo, criei uma procedure que vai fazer um SELECT na tabela temporária local que criamos anteriormente. Aqui devemos utilizar a mesma sessão, caso contrário a tabela não será encontrada.

USE Traces

GO

CREATE PROCEDURE stpTeste_1_Escopo_Temp_Local
AS
BEGIN
	SELECT * FROM #TABELA_TESTE_TEMPORARIA
END
GO

EXEC stpTeste_1_Escopo_Temp_Local

USE Traces

CREATE PROCEDURE stpTeste_1_Escopo_Temp_Local

BEGIN

SELECT * FROM #TABELA_TESTE_TEMPORARIA

END

EXEC stpTeste_1_Escopo_Temp_Local

Como podemos ver, a procedure foi executada normalmente! =)

Exemplo 2:

Agora, vamos criar uma outra tabela temporária local, mas dessa vez dentro de uma outra procedure.

USE Traces

GO

CREATE PROCEDURE stpTeste_2_Escopo_Temp_Local
AS
BEGIN
	CREATE TABLE #TABELA_TESTE_TEMPORARIA_PROCEDURE (
		Id_Teste INT IDENTITY(1,1),
		Nome VARCHAR(100)
	)

	INSERT INTO #TABELA_TESTE_TEMPORARIA_PROCEDURE
	VALUES('TESTE 2 - TABELA TEMPORÁRIA DENTRO DA PROCEDURE')

	SELECT * FROM #TABELA_TESTE_TEMPORARIA_PROCEDURE
END
GO

EXEC stpTeste_2_Escopo_Temp_Local

USE Traces

CREATE PROCEDURE stpTeste_2_Escopo_Temp_Local

BEGIN

CREATE TABLE #TABELA_TESTE_TEMPORARIA_PROCEDURE (

Id_Teste INT IDENTITY(1,1),

Nome VARCHAR(100)

)

INSERT INTO #TABELA_TESTE_TEMPORARIA_PROCEDURE

VALUES('TESTE 2 - TABELA TEMPORÁRIA DENTRO DA PROCEDURE')

SELECT * FROM #TABELA_TESTE_TEMPORARIA_PROCEDURE

END

EXEC stpTeste_2_Escopo_Temp_Local

Como podemos ver, a procedure executa normalmente e retorna o resultado. Entretanto, se você tentar fazer um SELECT nessa tabela temporária fora da procedure, será gerado um erro informando que ela não existe, pois o escopo dela é apenas dentro da procedure que já foi removida da CALL STACK.

SELECT * FROM #TABELA_TESTE_TEMPORARIA_PROCEDURE

1	SELECT * FROM #TABELA_TESTE_TEMPORARIA_PROCEDURE

Msg 208, Level 16, State 0, Line 119

Invalid object name ‘#TABELA_TESTE_TEMPORARIA_PROCEDURE’.

Exemplo 3 – CALL STACK:

Por fim, tentei resumir a execução com um trecho de código e com uma representação da CALL STACK.

Repare que a tabela temporária “#TABELA_TESTE_TEMPORARIA” é criada no início do Bloco 1 e fica disponível na CALL STACK até o final da sessão.

No Bloco 2, a procedure “stpTeste_1_Escopo_Temp_Local” é inserida na CALL STACK, executa e depois é removida.

No Bloco 3, a outra procedure “stpTeste_2_Escopo_Temp_Local” repete o mesmo procedimento na CALL STACK, sendo incluída, executada e depois removida (inclusive a tabela temporária que foi criada dentro dela).

Por fim, o Bloco 1 será removido da CALL STACK quando a sessão for finalizada ou quando a tabela temporária for dropada explicitamente.

Função “OBJECT_ID” x tempdb

Eu gosto bastante de utilizar o “IF” abaixo com a função “OBJECT_ID” para validar se uma tabela já existe, se ela já existir eu faço um DROP TABLE e depois o CREATE TABLE.

IF(OBJECT_ID('NomeTabela') IS NOT NULL)
	DROP TABLE NomeTabela

CREATE TABLE NomeTabela (
	.
	.
	.
)

IF(OBJECT_ID('NomeTabela') IS NOT NULL)

DROP TABLE NomeTabela

CREATE TABLE NomeTabela (

)

Isso evita o erro abaixo, caso a tabela já esteja criada.

Msg 2714, Level 16, State 6, Line 10

There is already an object named ‘NomeTabela’ in the database.

Vou mostrar uma situação quando utilizamos as tabelas temporárias e a função “OBJECT_ID”.

USE Traces

SELECT OBJECT_ID('#TABELA_TESTE_TEMPORARIA')

GO

USE tempdb

SELECT OBJECT_ID('#TABELA_TESTE_TEMPORARIA')

USE Traces

SELECT OBJECT_ID('#TABELA_TESTE_TEMPORARIA')

USE tempdb

SELECT OBJECT_ID('#TABELA_TESTE_TEMPORARIA')

Repare que a função só encontra a tabela temporária “#TABELA_TESTE_TEMPORARIA’” quando executamos na database “tempdb”. Ao executar conectado na database ‘Traces’ o resultado é NULL.

Para contornar esse problema quando você utilizar uma tabela temporária, basta utilizar a função da seguinte forma:

OBJECT_ID(‘tempdb..NomeTabela’)

OBS: Recomendo que utilize sempre o ‘tempdb’ minúsculo, pois se você colocar alguma letra maiúscula (por exemplo, Tempdb) o resultado pode ser FALSO, caso a database ‘tempdb’ esteja utilizando alguma COLLATION com CASE SENSITIVE (ou seja, diferencia letras maiúsculas e minúsculas).

USE Traces

IF(OBJECT_ID('tempdb..#TABELA_TESTE_TEMPORARIA') IS NOT NULL)
	SELECT 'Esse IF valida no tempdb e vai ser VERDADEIRO!' AS Resultado

IF(OBJECT_ID('#TABELA_TESTE_TEMPORARIA') IS NOT NULL)
	SELECT 'Esse IF NÃO valida no tempdb e vai ser FALSO!' AS Resultado

USE Traces

IF(OBJECT_ID('tempdb..#TABELA_TESTE_TEMPORARIA') IS NOT NULL)

SELECT 'Esse IF valida no tempdb e vai ser VERDADEIRO!' AS Resultado

IF(OBJECT_ID('#TABELA_TESTE_TEMPORARIA') IS NOT NULL)

SELECT 'Esse IF NÃO valida no tempdb e vai ser FALSO!' AS Resultado

Portanto, o que eu recomendaria nesse caso seria o script abaixo.

OBS: No DROP TABLE você NÃO precisa especificar o ‘tempdb’ antes do nome da tabela.

IF(OBJECT_ID('tempdb..#TABELA_TESTE_TEMPORARIA') IS NOT NULL)
	DROP TABLE #TABELA_TESTE_TEMPORARIA

1 2	IF(OBJECT_ID('tempdb..#TABELA_TESTE_TEMPORARIA') IS NOT NULL) DROP TABLE #TABELA_TESTE_TEMPORARIA

Referências:

Segue abaixo mais alguns links como referência:

https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

https://www.red-gate.com/simple-talk/sql/t-sql-programming/temporary-tables-in-sql-server/

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais

Tabelas Temporárias:

Tabelas Temporárias Locais

Definição e Nomenclatura da Tabela Temporária:

Escopo de uma Tabela Temporária Local:

Função “OBJECT_ID” x tempdb

Referências:

2 comentários em “Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais”

Deixe uma respostaCancelar resposta

Tabelas Temporárias:

Tabelas Temporárias Locais

Definição e Nomenclatura da Tabela Temporária:

Escopo de uma Tabela Temporária Local:

Função “OBJECT_ID” x tempdb

Referências:

Compartilhe isso:

2 comentários em “Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais”

Deixe uma respostaCancelar resposta