Tabele tymczasowe w Transact-SQL

Czym są tabele tymczasowe w T-SQL?

Tabele tymczasowe w Transact-SQL to tabele, które. Tabele tymczasowe w odróżnieniu od widoków posiadają dane fizyczne. Są one jednak kasowane po zamknięciu wszystkich sesji, w których tabela jest użytkowana. Dane tabel tymczasowych przechowywane są w bazie tempdb. Znajdziemy je przechodząc w drzewie obiektów SSMS. Wybierając Databases > System databases > tempdb > Temporary Tables.

W SQL Server mamy do wyboru 2 rodzaje tabel tymczasowych: lokalne i globalne.

• Tabele tymczasowe lokalne w SQL Server działają w obrębie danej sesji. Może z nich korzystać tylko i wyłącznie użytkownik, który utworzył daną tabelę. Po zamknięciu sesji tabela tymczasowa jest automatycznie kasowana. Lokalne tabele tymczasowe w SQL Server tworzymy jak tabele zwykłe poprzedzając ich nazwę znakiem hash (#).
• Tabele tymczasowe globalne w SQL Server działają także w innych sesjach i mogą być dostępne dla innych użytkowników, jeśli nadamy tym użytkownikom odpowiednie uprawnienia. Tabela tymczasowa globalna jest automatycznie kasowana po zamknięciu wszystkich sesji, w których bierze udział. Globalne tabele tymczasowe w SQL Server tworzymy jak tabele zwykłe poprzedzając ich nazwę dwoma znakami hash (##).

Operacje DDL na tabelach tymczasowych

Na tabelach tymczasowych możemy dokonywać dowolne operacje DDL, takie jak CREATE, ALTER, DROP. Możemy także używać constraintów.

--tomaszkenig.pl
CREATE TABLE #tabTest (id int PRIMARY KEY identity (1,1),
MyValue varchar(255) NOT NULL);

ALTER TABLE #tabTest
ADD Country varchar(50);

SELECT * FROM #tabTest

DROP TABLE #tabTest;

Operacje DML na tabelach tymczasowych

Na tabelach tymczasowych możemy wykonywać dowolne operacje DML takie jak INSERT, UPDATE, DELETE. Poniżej przykład utworzenia lokalnej tabeli tymczasowej, do której insertujemy dane. Następnie zmieniamy wartości w jednym z rekordów za pomocą polecenia UPDATE. Kasujemy też jeden z rekordów. Na koniec wyświetlamy wynik działań i kasujemy tabelę za pomocą polecenia SQL DROP table.

--tomaszkenig.pl
CREATE TABLE #tabTest (id int identity (1,1),
MyValue varchar(255));

insert into #tabTest (MyValue) values ('New York');
insert into #tabTest (MyValue) values ('Tokyo');
insert into #tabTest (MyValue) values ('London');

UPDATE #tabTest SET MyValue = 'Sydney' where id=4;

DELETE FROM #tabTest WHERE ID = 2;

select * from #tabTest

drop table #tabTest;

Korzystanie z tabel tymczasowych przez innych użytkowników

Na początek utwórzmy globalną tabelę tymczasową. Jak wcześniej wspomniałem poprzedzamy nazwę tabeli dwoma znakami hash (##). By mieć dostęp do tabel tymczasowych utworzonych przez innych użytkowników, nie jest wymagany grant.

--tomaszkenig.pl
CREATE TABLE ##tabTest (id int identity (1,1),
MyValue varchar(255));

insert into ##tabTest (MyValue) values ('New York');
insert into ##tabTest (MyValue) values ('Tokyo');
insert into ##tabTest (MyValue) values ('London');

select * from ##tabTest;

Jeśli utworzyłeś globalną tabelę tymczasową, dostęp do niej mają wszyscy użytkownicy posiadający posiadający rolę public. Można się odwoływać poprzez zwykłe polecenie SQL select.

--tomaszkenig.pl
select * from ##tabTest;

lub

--tomaszkenig.pl
select * from tempdb.dbo.##tabTest;

Tabele tymczasowa globalna zostanie automatycznie usunięta po zakończeniu ostatniej sesji, która z niej korzysta.

Ten artykuł jest częścią szkolenia T-SQL, które zamieszczam pod tym adresem: kurs Transact-SQL. Możesz także skorzystać z całego kursu dotyczącego SQL Server, który zamieszczam tutaj: Kurs MsSQL.