Armazenamento Temporário e CTEs - SQL Server

Armazenamento Temporário eCommon Table Expressions (CTE)

Agenda

1. Variáveis T-SQL

2. Tabelas Temporárias

3. Variáveis tipo TABLE

4. CTE

5. CTE Recursiva

6. DEMOS

7. Perguntas ?

Variáveis T-SQLUma variável local Transact-SQL é um objeto que pode conter um valor de dados de um tipo específico. As variáveis em lotes e scripts são normalmente usadas:

Como um contador, para contar o número de vezes que um loop é executado ou controlar quantas vezes que o loop é executado.

Para reter um valor de dados a ser testado por uma instrução de controle de fluxo.

Para salvar um valor de dados a ser retornado por um código de retorno de procedimento armazenado ou valor de retorno de função.

Fonte: http://msdn.microsoft.com/pt-br/library/ms187953(v=sql.105).aspx

Variáveis T-SQL

Tabelas TemporáriasHá dois tipos de tabelas temporárias: local e global.

Elas diferem uma da outra pelo nome, visibilidade e disponibilidade.

As tabelas temporárias locais têm um único sinal (#) como primeiro caractere no nome; elas são visíveis somente na conexão atual para o usuário e são excluídas quando o usuário se desconecta da instância do SQL Server.

As tabelas temporárias globais têm dois sinais (##) como primeiros caracteres no nome; elas são visíveis a qualquer usuário após serem criadas e são excluídas quando todos os usuários que consultam a tabela se desconectam da instância do SQL Server.

Tabelas Temporárias

São gravadas fisicamente, armazenadas no banco de sistema (tempdb)

Permite utilizar índices (o que pode gerar ganho de performance)

São visíveis apenas enquanto a conexão está ativa

TempDB: http://msdn.microsoft.com/pt-br/library/ms190768.aspx

Tabelas Temporárias

Variáveis tipo TABLE Criadas apenas dentro do escopo (da procedure, trigger ou do lote que foram construídas)

Armazenadas em disco!

Gasta menos recursos com bloqueio

As operações são minimamente logadas (consumindo então menos recurso de log)

Quando utilizadas em stored procedures, geram menos recompilações do que se fossem utilizadas tabelas temporárias

Não podem ser alvos de SELECT/INTO ou INSERT/EXEC

Não podem ter índices não-cluster

Não é possível criar e/ou atualizar estatísticas

Não se pode mudar sua estrutura depois que ela for criada (adicionar uma coluna, por exemplo)

Variáveis tipo TABLE

Links

Perguntas freqüentes - variáveis de tabela

SQL SERVER – Difference TempTable and Table Variable –

TempTable in Memory a Myth

CTEChamamos de common table expression o conjunto de resultados

temporário que se define no escopo de execução de uma instrução

SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW.

As CTEs não são armazenadas como objetos e conservam-se

apenas durante a consulta, sendo, por estes motivos, parecidas

com as tabelas derivadas (ou variáveis tipo TABLE).

CTEO uso de CTEs está voltado para as seguintes finalidades:

Para criar uma consulta recursiva;

Para substituir uma view nas situações em que seu uso geral

não é exigido;

Para habilitar o agrupamento por uma coluna derivada em um

subselect escalar;

Para referenciar diversar vezes na mesma instrução a tabela

resultante;

CTE Usar uma CTE oferece as vantagens de legibilidade aprimorada e facilidade de manutenção de consultas complexas. A consulta pode ser dividida em blocos de construção lógicos simples e separados. Esses blocos simples podem ser usados para criar CTEs interinas mais complexas, até que o resultado final seja gerado.

As CTEs podem ser definidas em rotinas definidas pelo usuário, como funções, procedimentos armazenados, gatilhos ou exibições. CTEs também podem ser aninhadas.

Estrutura de uma CTEUma CTE é constituída de um nome de expressão representando a CTE, uma lista de colunas opcional e uma consulta que define a CTE. Após a definição da CTE, ela poderá ser referenciada como uma tabela ou exibição em uma instrução SELECT, INSERT, UPDATE ou DELETE. Uma CTE também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.

A estrutura de sintaxe básica de uma CTE é:

Relatório de Pedidos

Relatório de Pedidos

CTE RecursivaCom as CTEs também é possível escrever consultas recursivas, as quais são formadas por três elementos: a invocação da rotina, a invocação recursiva da rotina e a verificação de finalização.

Uma CTE recursiva é capaz de retornar diversas linhas, ao contrario de uma rotina recursiva de outras linguagens, que retorna um valor escalar.

Enquanto criamos a CTE, podemos modificar sua consulta. Para isto basta criar a consulta do membro âncora, adicionar o operador UNION ALL e, então, criar a consulta do membro recursivo que fala auto-referência à CTE.

CTE Recursiva

CTE Recursiva: http://msdn.microsoft.com/pt-br/library/ms186243(v=sql.105).aspx

CTE Recursiva