Tabele tymczasowe i zmienne tablicowe – fakty i mity

  • Artykuł

Udostępnij na: Facebook

Autorzy: Damian Widera, Łukasz Grala

Opublikowano: 2010-11-15

Wstęp

Wokół zmiennych tablicowych narosło wiele mitów, począwszy od nieprawidłowej interpretacji czym są, a czym nie są zmienne tablicowe, a kończąc na porównaniu tego rodzaju zmiennych do tabel tymczasowych.

W Internecie można znaleźć wiele informacji na temat tabel tymczasowych i zmiennych tablicowych, jednak często są one nieprawdziwe, a w najlepszym przypadku niepełne. W naszym artykule omówione zostaną wspomniane dwa rodzaje obiektów oraz wskazane przykłady, kiedy należy wybrać tabele tymczasowe, a kiedy posłużyć się zmiennymi tablicowymi.

Tabele tymczasowe

Pojęcie tabeli tymczasowej jest nierozerwalnie związane z pojęciem sesji. Wystarczy spojrzeć do narzędzia SQL Server Management Studio (SSMS), aby stwierdzić, że każde zapytanie otwarte w nowym oknie rozpoczyna nową sesję, czyli nowe połączenie do SQL Server. Aplikacje mogą korzystać z wielu sesji podczas pobierania czy manipulacji danymi w SQL Server, mogą również dzielić jedno aktywne połączenie z innymi programami. Możliwa jest również taka komunikacja, w której każde zapytanie otwiera połączenie i natychmiast je zamyka po otrzymaniu wyniku.

Tabele tymczasowe (zgodnie z BOL) tworzy się w taki sam sposób, jak inne tabele użytkownika, mając na uwadze kilka ograniczeń. SQL Server pozwala na utworzenie dwóch rodzajów tabel tymczasowych:

  • globalnych, których nazwa jest poprzedzona prefiksem ##, oraz
  • lokalnych, których nazwa jest poprzedzona prefiksem #.

Obydwa rodzaje tabel tymczasowych tworzone są fizycznie w bazie danych tempdb.

Lokalne tabele tymczasowe są widoczne w ramach jednego połączenia, czyli sesji. Należy pamiętać, że nazwa tabeli tymczasowej – lokalnej czy globalnej – jest ograniczona do 116 znaków, zamiast 128 jak w przypadku tabel użytkownika. Jest to związane z procesem tworzenia tabel tymczasowych. Silnik baz danych musi mieć możliwość identyfikacji każdego obiektu, a tabele tymczasowe istniejące w wielu połączeniach mogą mieć takie same nazwy. Z tego względu SQL Server dokleja wewnętrzny numer (jako sufiks) do każdej nazwy tabeli tymczasowej. W efekcie wygląda to tak, że po utworzeniu tabel o takich samych nazwach w różnych połączeniach:

create table #tab(id int)

otrzymujemy informację z widoku sys.tables w bazie danych tempdb:

use tempdb

go

select name from sys.tables

--------

name

-----------#tab________________________________________________________________________________________________________________000000000003

#tab________________________________________________________________________________________________________________000000000004

(2 row(s) affected)

Lokalna tabela tymczasowa jest dostępna dla użytkownika natychmiast po utworzeniu, także wewnątrz zagnieżdżonych procedur składowanych. Przyjrzyjmy się  problemom procedur zagnieżdżonych procedur składowanych oraz tabelom tymczasowym nieco bliżej. Rozważmy najpierw sytuację, gdy w procedurze nadrzędnej spróbujemy odwołać się do tabeli tymczasowej utworzonej w procedurze podrzędnej.

Utwórzmy procedurę składowaną NestedTestTempTables, która ma następującą postać:

CREATE PROCEDURE NestedTestTempTables

AS

BEGIN

      -- akcje użytkownika

      CREATE TABLE #temp(id int)

      --inne akcje

END

GO

Teraz utwórzmy procedurę ErrorTestTempTables, która wywołuje wcześniej utworzoną procedurę NestedTestTempTables, a następnie próbuje wstawić dane do tabeli tymczasowej #temp

CREATE PROCEDURE ErrorTestTempTables

AS

BEGIN

      --wywoływana jest procedura, w której tworzona jest tabela tymczasowa

      --#temp

      EXEC NestedTestTempTables

      --tabela tymczasowa #temp utworzona w procedurze podrzędnej NIE jest dostępna w procedurze nadrzędnej

      INSERT INTO #temp

      SELECT 1

      SELECT * FROM #temp

END

GO

Powyższe procedury skompilują się, ale próba wywołania procedury ErrorTestTempTables zakończy się błędem:

Msg 208, Level 16, State 0, Procedure ErrorTestTempTables, Line 12

Invalid object name '#temp'.

Można problem odwrócić i przeanalizować sytuację, w której procedura nadrzędna tworzy tabelę tymczasową dostępną do wszystkich procedur podrzędnych.  Dla zilustrowania tego przypadku utwórzmy procedurę ExistingNestedTestTempTables, w której korzystamy z istniejącej tabeli tymczasowej #temp i wstawiamy do niej nowe wiersze:

CREATE PROCEDURE ExistingNestedTestTempTables

AS

BEGIN

      -- akcje użytkownika

-- korzystamy z tabeli tymczasowej utworzonej w innej procedurze składowanej

      INSERT INTO #temp

      VALUES (3,5),(4,7),(8,9)

--inne akcje

END

GO

Procedura TestTempTables tworzy tabelę tymczasową #temp, wstawia do niej jeden wiersz i wywołuje opisaną wcześniej procedurę ExistingNestedTestTempTables. Zgodnie z założeniami procedura podrzędna może skorzystać z utworzonej wcześniej tabeli tymczasowej i wykonywać na niej wymagane operacje. Należy jednak pamiętać, że tego typu sposób pisania procedur i tworzenia tabel tymczasowych niesie ze sobą potencjalne niebezpieczeństwo wystąpienia błędów – jeśli obiekt (tabela tymczasowa) nie zostanie utworzony w odpowiednim momencie.

CREATE PROCEDURE TestTempTables

AS

BEGIN

      --wywoływana jest procedura, w któej tworzona jest tabela tymczasowa

      -- #temp

      EXEC NestedTestTempTables

      --próba utworzenia nowej tabeli #temp – bo nie ma dostępu do tabeli utworzonej w procedurze NestedTestTempTables

      CREATE TABLE #temp(id int, id1 int)

      -- Obiekt #temp jest dostępny

      INSERT INTO #temp

      SELECT 1,2

      EXEC ExistingNestedTestTempTables

      SELECT * FROM #temp

END

GO

Po uruchomieniu procedury TestTempTables otrzymamy następujący wynik:

(1 row(s) affected)

(3 row(s) affected)

id          id1

----------- -----------

1           2

3           5

4           7

8           9

(4 row(s) affected)

Lokalne tabele tymczasowe zostaną usunięte w następujących sytuacjach:

  1. zostanie wywołane polecenie DROP TABLE,
  2. jeśli tabela tymczasowa została utworzona wewnątrz procedury składowanej, to zostanie usunięta po zakończeniu działania procedury,
  3. wszystkie inne tabele tymczasowe zostaną usunięte po zakończeniu sesji, w której zostały utworzone.

Globalne tabele tymczasowe (utworzone z przedrostkiem ##) mają większą widoczność, ponieważ są dostępne dla wszystkich sesji. Należy jednak zawsze sprawdzać, czy globalna tabela tymczasowa istnieje zanim zostanie utworzona ponownie, ponieważ w takim wypadku SQL Server zgłosi komunikat o błędzie. Globalna tabela tymczasowa jest usuwana w momencie, kiedy sesja, w której została utworzona tabela tymczasowa, została zakończona i tabela nie posiada referencji (odwołań) w innych aktywnych sesjach.

Dla lepszego zobrazowania problemu wykonajmy poniższy kod:

  1. W nowym oknie SSMS wpisz i uruchom polecenie:

    CREATE TABLE ##temp(ID int).

  2. W drugim oknie SSMS wpisz i uruchom drugie polecenie, które co 5 sekund będzie pobierało dane z utworzonej wcześniej tabeli ##temp:

    WHILE 1=1
    BEGIN
    SELECT * FROM ##temp
    WAITFOR DELAY '00:00:05'
    END

  3. Wróć do pierwszego okna i zamknij je.

  4. Podczas następnego uruchomienia pętli SQL Server zgłosi błąd, ponieważ została zamknięta sesja  tworząca tabelę ##temp i nie było aktywnych referencji do niej:

    Msg 208, Level 16, State 0, Line 3
    Invalid object name '##temp'.

Należy więc mieć na uwadze tego typu zachowanie lokalnych i globalnych tabel tymczasowych podczas projektowania aplikacji. Należy też pamiętać, że tabele tymczasowe:

  1. nie mogą być partycjonowane,
  2. nie mogą posiadać klucza obcego (foreign key constraint),
  3. kolumny tabeli tymczasowej nie mogą być typami zdefiniowanymi przez użytkownika (UDDT), o ile nie są utworzone w bazie danych tempdb. Typy UDDT są widzialne w ramach bazy danych, w której zostały utworzone, a ponieważ baza danych tempdb jest odtwarzana po każdym uruchomieniu serwera, to typy danych użytkownika można dodać na dwa sposoby:
    • wykorzystać procedurę startową, która wstawi typy UDDT do bazy danych tempdb w momencie uruchomienia usługi SQL Server, lub
    • wstawić typy UDDT do bazy danych model, ponieważ ta baza jest wzorcem dla każdej nowo tworzonej bazy danych, więc także dla tempdb,
  4. kolumny typu XML nie mogą być zdefiniowane za pomocą kolekcji XML, chyba że wcześniej taka kolekcja została dodana do bazy danych tempdb.

Tabele tymczasowe mogą być tworzone za pomocą polecenia

CREATE TABLE #tabela

lub

SELECT <lista kolumn> INTO #tabela

Można również użyć polecenia:

INSERT INTO #tabela EXEC "procedura składowana"

Tabele tymczasowe mogą mieć indeksy oraz ograniczenia (constraints), którym można nadawać nazwy. Jednak w przypadku uruchomienia procedury składowanej, w której tworzona jest tabela tymczasowa oraz nazwane indeksy czy ograniczenia, w więcej niż jednej sesji równocześnie, system zgłosi błąd związany z istnieniem obiektu – indeksu lub ograniczenia:

There is already an object named '<objectname>' in the database

Rozwiązaniem tego problem jest zezwolenie na nazwanie indeksów czy ograniczeń przez SQL Server, ponieważ w takiej sytuacji to na SQL Server spocznie konieczność zapewnienia tym obiektom unikalnych nazw.

Warto również zapoznać się z artykułem, w którym omówiono wpływ użycia tabel tymczasowych w procedurach składowanych na rekompilację tych procedur oraz wskazano sposoby jej uniknięcia, a przynajmniej minimalizację tego problemu. 

Zmienne tablicowe

Zmienne tablicowe zostały wprowadzone w SQL Server 2000. Zmienna tablicowa jest to zmienna, która może przechować tablicę – zawiera kolumny posiadające nazwy, będące nazwami określonych typów danych, oraz może mieć ograniczenia. Ograniczeniami dla zmiennych tablicowych mogą być:

  • PRIMARY KEY
  • UNIQUE
  • NULL
  • CHECK

Zmienne tablicowe nie mogą używać ograniczeń FOREIGN KEY.

Tworzenie zmiennej tablicowej jest identyczne jak w przypadku tworzenia innej zmiennej:

DECLARE @tabela TABLE (ID int)

Zmienne tablicowe mają, podobnie jak tabele tymczasowe, dobrze zdefiniowany zasięg widoczności. Jest między tymi obiektami różnica polegająca na tym, że zmienne tablicowe nie są widoczne w procedurach czy funkcjach podrzędnych po tym, jak zostały utworzone w procedurze nadrzędnej. Zmienne tablicowe są usuwane najpóźniej podczas zakończenia sesji, w ramach której zostały utworzone; jeśli w ramach sesji wykonanych było kilka procedur składowanych, to zasięg zmiennej tablicowej ogranicza się do jednej procedury i po jej zakończeniu zmienna jest usuwana.

W przypadku gdyby trzeba było użyć zmiennej tablicowej w dynamicznym kodzie TSQL, to taka zmienna musi być zadeklarowana wewnątrz tego kodu – inaczej, niż w przypadku tabeli tymczasowej.

Warto wspomnieć jeszcze o kilku istotnych sprawach:

  1. Zmienne tabelaryczne powodują znacznie mniej rekompilacji procedur składowanych – zgodnie z artykułem w bazie wiedzy firmy Microsoft.
  2. Transakcje wykonywane na zmiennych tablicowych ograniczają się do operacji UPDATE, co powoduje znaczne zmniejszenie blokad czy zakleszczeń oraz zdecydowanie mniej logowania w dzienniku transakcji.
  3. Zmienne tablicowe nie stanowią o spójności bazy danych, w której  się znajdują, a więc nie działają na nie operacje ROLLBACK TRANSACTION.
  4. Zmiennej tablicowej nie można przypisywać do innej zmiennej tablicowej.
  5. Zmienna tablicowa nie może w swojej strukturze odwoływać się do typów danych użytkownika (UDDT).
  6. SQL Server nie buduje statystyk dla zmiennych tablicowych, wobec czego optymalizator uznaje, że zmienna tablicowa zawsze ma 1 wiersz (niezależnie od faktycznej ilości przechowywanych danych).
  7. Nie można wykonywać operacji TRUNCATE TABLE na zmiennej tablicowej.
  8. Nie można tworzyć nazwanych ograniczeń dla zmiennej tablicowej.
  9. Nazwa zmiennej tabelarycznej zostanie wewnętrznie nadana przez SQL Server.
  10. Jeśli zmienna tablicowa posiada kolumnę typu IDENTITY, to nie można do niej wpisywać wartości wprost, ponieważ nie jest wspierana instrukcją SET IDENTITY_INSERT ON.

Powyższe informacje nie zmieniają jednak faktu, że zmienne tablicowe można używać jak każde inne tabele. Mogą być więc częścią wszystkich instrukcji DML, takich jak SELECT, INSERT, UPDATE, DELETE. Należy jednak pamiętać, że dla zmiennych tabelarycznych nie można użyć instrukcji:

SELECT <lista kolumn> INTO @tabela

która była dozwolona dla tabel tymczasowych. Wiąże się to z faktem, że zmienna tabelaryczna musi mieć zdefiniowaną strukturę w momencie tworzenia i struktury tej nie można już zmieniać.

Mity

Omówione zostaną trzy najpopularniejsze mity, dotyczące tabel tymczasowych i zmiennych tablicowych:

  1. Zmienne tablicowe mogą istnieć tylko w pamięci i nie są materializowane na dysku.
  2. Tabele tymczasowe istnieją tylko na dysku i nie są przechowywane w pamięci.
  3. Zmienne tablicowe nie mogą mieć żadnych indeksów.

Mity pierwszy  i drugi są ze sobą blisko związane i obydwa są nieprawdziwe. Zarówno zmienne tablicowe, jak i tabele tymczasowe tworzone są i przechowywane w bazie danych tempdb. Prawdą natomiast jest, że zarówno tabele tymczasowe, jak i zmienne tabelaryczne są tworzone w pamięci dostępnej dla SQL Server. Z drugiej strony, zmienna tabelaryczna może przechować więcej informacji (do 2 GB) niż jest w danej chwili dostępnej pamięci, a więc zostanie zmaterializowana na dysku. Takie operacje materializacji mogą być przeprowadzane przez silnik baz danych w każdej chwili.

Przeanalizujmy poniższy kod:

USE tempdb

GO

-- wypisz wszystkie tabele użytkownika z bazy tempdb

-- przechowaj je w tabeli tymczasowej

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

      DROP TABLE #tempTable

SELECT name INTO #tempTable

FROM sys.tables

WHERE TYPE='U'

-- wyświetl wszystkie tabele tymczasowe

-- na liście będzie na pewno tabela #tempTable

-- (uwaga - tutaj mogą byc tabele z innych sesji)

SELECT * FROM  #tempTables

GO

-- Utwórz zmienną tablicową

DECLARE @MyTableVariable TABLE  (ID int)

-- Sprawdź, że zmienna tablicowa znajduje się na liście tabel w bazie

-- danych tempdb

SELECT name FROM sys.tables

WHERE TYPE='U'

GO

Najpierw tworzona jest lista wszystkich obiektów użytkownika w bazie danych tempdb, a informacja ta jest zapisywana w tabeli tymczasowej #tempTable,dzięki czemu ta tabela również znajdzie się na liście. Następnie tworzona jest zmienna tablicowa @MyTableVariable, która również znajduje się w bazie danych tempdb.

Mit trzeci – mówiący, że zmienne tablicowe nie mogą mieć żadnych indeksów – również jest fałszywy. Prawdą jest tylko, że zmienna tablicowa może mieć indeks, który wynika z zastosowania ograniczenia PRIMARY KEY lub UNIQUE, pod warunkiem że zostanie utworzony w momencie deklarowania zmiennej tablicowej:

DECLARE @MyTableVariable TABLE

(ID int PRIMARY KEY CLUSTERED)

Po zadeklarowaniu zmiennej tablicowej żadne operacje DDL, a więc również instrukcja CREATE INDEX, nie mogą być wykonywane.

Firma Microsoft sugeruje użycie zmiennych tablicowych w sytuacji, kiedy liczba wierszy będzie mała. Wiąże się to z faktem, że dla zmiennych tablicowych nie są tworzone statystyki i.  Dla około 100 wierszy brak statystyk nie jest jeszcze tak znaczący, a należy pamiętać, że optymalizator zawsze przyjmie, że zmienna tablicowa ma 1 wiersz. Zmienna tablicowa może mieć indeks wynikający z użycia ograniczenia PRIMARY KEY lub UNIQUE, a więc zapytania odpowiednio korzystające z tego indeksu będą przetwarzały się wydajnie. Jeśli jednak trzeba będzie dokonywać złączenia zmiennej tablicowej z innymi tabelami, to brak statystyk będzie powodował generowanie tym bardziej nieefektywnego planu zapytania, im więcej wierszy przechowuje zmienna tablicowa.

Podsumowanie

Zmienne tablicowe i tabele tymczasowe stały się na dobre jednym z fundamentów programowania w SQL Server i trudno sobie wyobrazić ich brak. Decyzja, które z nich należy użyć, zależy od konkretnego przypadku, z którym przychodzi się zmierzyć. Każdy z omawianych typów obiektów ma swoje specyficzne cechy,  które determinują jego użycie. W wielu przypadkach konieczne będzie jednak doświadczenie administratora czy programisty, aby dobrze wybrać: tabela tymczasowa, czy zmienna tablicowa.