quinta-feira, 15 de agosto de 2013

O que são tabelas temporárias? Para que servem? Como se criam?

O que é uma Tabela Temporária Local?
É uma tabela semelhante em tudo às tabelas "normais" mas com algumas particularidades: o seu tempo de vida é igual ao da ligação (connection) usada para a sua criação. Ou seja quando a "connection" for fechada a tabela deixa de existir e além disso só pode ser acedida no âmbito dessa "connection", daí ser local à "connection".

Com efeito as Tabelas temporárias são criadas fisicamente numa Base de Dados de sistema designada por tempdb.
Mas em termos de implementação isso não é particularmente relevante, visto que para todos os efeitos eu crio-a e uso-a apenas "dentro" da minha "connection".

Assim perante a pergunta que neste momento pode estar a bailar-lhe nos lábios: "Então posso criar várias tabelas temporárias locais com o mesmo nome numa BD?", a resposta é: SIM!

Isto porque internamente o SQL Server adiciona um sufixo a cada uma das tabelas de modo a saber qual diz respeito a que sessão.

Ok, agora que já sabemos o que é uma tabela temporária local, para que serve?
Estas tabelas são habitualmente usadas como tabelas auxiliares em determinados processos que envolvam as chamadas operações de "corte e costura".
Vou deixar aqui apenas alguns exemplos:

1. Conciliar dados provenientes de várias tabelas e aplicando-lhes  cálculos e/ou agregações, admitindo que esse resultado é suposto ser usado várias vezes no âmbito da mesma "connection". (Ganha-se performance)

2. Para permitir fazer filtros à cabeça i.e. imagine que possui duas tabelas com milhões de registos e precisa de as juntar, no entanto após a junção que produz ainda muitos registos existe uma cláusula WHERE (filtro) que vem reduzir substancialmente a quantidade de registos devolvidos. O problema é que o filtro pode ser aplicado após a junção e esta consome imenso tempo!
As tabelas temporárias podem ser usadas de modo a que seja aplicado primeiro o filtro a cada tabela original e passado o respectivo resultado para estas e depois juntam-se as tabelas temporárias que porque já têm muito menos    registos produzem o resultado final mais rapidamente. (Ganha-se performance)
 
3. Podem ser usadas em Stored Procedures para que no final seja devolvido um só "resulset" com toda a informação que foi sendo criada por exemplo por um ou mais processos iterativos dentro do SP ao longo da sua execução.

4. etc.

Nos meus exemplos utilizo várias vezes estas tabelas porque me permitem fazer demonstrações sem "invadir" as Bases de Dados com lixo, uma vez que a tabela e todo o conteúdo desaparece ao fechar a ligação.

O que é uma tabela temporária Global?Conceptualmente é parecida com a local mas com uma diferença substâncial: o seu âmbito.
Ou seja a Global é criada também ao nível de uma "connection" mas está acessível a todas as "connections" que entretanto sejam abertas e lhe pretendam aceder.
Quando é que esta desaparece? Quando for fechada a última "connection" que está a utilizá-la.
No fundo a ideia é permitir partilha da mesma informação (já devidamente tratada) em várias connections.

Nota: Aqui não podem ser criadas tabelas com o mesmo nome.

Como se cria uma Tabela Temporária Local?A sintaxe em T-SQL é igual à de uma tabela normal com uma simples diferença: O nome da tabela começa com um #
Ex: CREATE TABLE #Tabela (c1 int)

Como se cria uma Tabela Temporária Global?A sintaxe em T-SQL é igual à de uma tabela normal com uma simples diferença: O nome da tabela começa com dois ##
Ex: CREATE TABLE ##Tabela (c1 int)

Nota: Estas tabelas (quer as locais quer as globais) suportam todas as operações normais SELECT, INSERT, UPDATE, DELETE, etc e inclusivamente suportam transacções explícitas e índices.

quarta-feira, 14 de agosto de 2013

Listando a localização física dos arquivos dos bancos de dados

Muitas vezes necessitamos identificar a localização física dos arquivos que compõem os bancos de dados, e ficar clicando com o botão direito do mouse em cada banco não é muito prático (imagine uma instância com 20 bancos). Todo esse esforço pode ser minimizado através da execução de uma simples query:
SELECT d.name, m.physical_name FROM sys.master_files m
inner join sys.databases d
on d.database_id = m.database_id