Zakres tabel tymczasowych i zmiennych tabelarycznych

SQL Serwer pozwala na użycie dwóch typów tabel tymczasowych - lokalnych i globalnych. Lokalne tabele tymczasowe są nazywane z prefiksem # np: #T1. Są one widoczne tylko w sesji która je stworzyła. Inne sesje mogą tworzyć tabele tymczasowe z taką samą nazwą i każda z nich widzi tylko swoją tabelę. Wewnątrz mechanizmów SQL Serwera takim tabelom nadawany jest unikalny sufiks aby nazwa była unikalna w bazie danych, lecz jest to przezroczyste dla sesji. Lokalne tabele tymczasowe są widoczne na poziomie który je stworzył, pomiędzy partiami kodu i na wszystkich wewnętrznych poziomach stosu wywołań. Więc jeżeli utworzymy tabelę na pewnym poziomie w kodzie i wykonamy dynamiczną partię kodu lub procedurę składowaną, to wewnętrzna partia ma również dostęp do tabeli tymczasowej. Jeżeli nie usuniemy tabeli tymczasowej bezpośrednio to zostanie ona zniszczona w chwili gdy poziom który ją stworzył zostanie zakończony. Globalne tabele tymczasowe są nazywane z prefiksem ## np: ##T1. Są one widoczne dla wszystkich sesji. Są niszczone kiedy sesja która je stworzyła jest zakończona i nie ma do nich żadnych aktywnych odwołań. Zmienne tabelaryczne są deklarowane w przeciwieństwie do tworzenia tabel tymczasowych. Są nazywane z prefiksem @ np: @T1. Zmienne tabelaryczne są widoczne tylko dla partii kodu która je stworzyła i są niszczone automatycznie w chwili zakończenia partii. Nie są widoczne pomiędzy partiami kodu na tym samym poziomie ani na wewnętrznych poziomach stosu wywołań. Poniższy kod demonstruje tworzenie tabeli tymczasowej lokalnej, jej widoczność między partiami kodu na tym samym poziomie oraz na wewnętrznych poziomach stosu wywołań:

CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
EXEC('SELECT col1 FROM #T1;');
GO
SELECT col1 FROM #T1;
GO
DROP TABLE #T1;
GO

Poniższy kod demonstruje deklarację zmiennej tabelarycznej oraz to, że zmienne tabelaryczne nie są widoczne na wewnętrznych poziomach stosu wywołań:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
EXEC('SELECT col1 FROM @T1;');
GO

Próba odwołania do zmiennej tabelarycznej z partii kodu dynamicznego wygeneruje następujący błąd:

Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@T1".

Poniższy kod demonstruje, że zmienne tabelaryczne nie są widoczne nawet pomiędzy partiami na tym samym poziomie stosu wywołań tej samej sesji:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
GO
SELECT col1 FROM @T1;
GO

Próba wykonania tego skryptu również zwraca błąd:

Msg 1087, Level 15, State 2, Line 2 Must declare the table variable "@T1".

DDL i indeksy w tabelach tymczasowych

Nazwy ograniczeń są tak jak nazwy wszystkich obiektów muszą być unikalne dla schematu. Dwie tabele nie mogą mieć ograniczeń o tej samej nazwie w tym samym schemacie. Tabele tymczasowe są tworzone w bazie danych TEMPDB w schemacie DBO. Jak już wspomniano, można utworzyć dwie tabele tymczasowe o takiej samej nazwie w dwóch różnych sesjach, ponieważ SQL Serwer dodaje swój wewnętrzny sufiks do każdej. Lecz niestety nie możliwe jest aby utworzyć tabele tymczasowe w różnych sesjach z taką samą nazwą ograniczenia. tylko jedna z nich zostanie utworzona a druga wygeneruje błąd. Wykonajmy skrypt poniżej w dwóch sesjach:

CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
CONSTRAINT PK_#T1 PRIMARY KEY(col1)
);

Uruchomienie tego skryptu w pierwszej sesji powiodło się. Druga sesja wygenerowała błąd:

Msg 2714, Level 16, State 5, Line 1 There is already an object named 'PK_#T1' in the database. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

Powodem błędu była powtórzona nazwa klucza głównego PK_#T1. Jeżeli utworzymy ograniczenie bez nazywania go to SQL Serwer wewnętrznie stworzy unikalną nazwę dla niego. Dlatego najlepszą praktyka jest nie nazywanie ograniczeń w tabelach tymczasowych. Ponizej znajduje się kod który można uruchomić w równoległych sesjach:

CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1)
);

SQL Serwer pozwala na tworzenie indeksów w tabelach tymczasowych po utworzeniu tych tabel. Poniższy kod tworzy indeks na utworzonej wcześniej tabeli tymczasowej:

CREATE UNIQUE NONCLUSTERED INDEX idx_col2 ON #T1(col2);

Można również zmienić definicję tabeli tymczasowej za pomocą komendy ALTER TABLE np dodając ograniczenie lub kolumnę. W przypadku zmiennych tabelarycznych SQL Serwer nie pozwala na nazywanie ograniczeń. Poniższy kod demonstruje próbę deklaracji zmiennej tabelarycznej z nazwanym ograniczeniem:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
CONSTRAINT PK_@T1 PRIMARY KEY(col1)
);

Wykonanie generuje błąd:

Msg 156, Level 15, State 2, Line 6 Incorrect syntax near the keyword 'CONSTRAINT'.

Spróbujmy wykonać ten kod beż nazywania ograniczenia:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1)
);

Tym razem zmienna została utworzona. SQL Serwer nie pozwala na tworzenie indeksów po deklaracji zmiennej tabelarycznej. Jednak należy zauważyć, że podczas definiowania klucza głównego SQL Serwer używa unikalnego indeksu klastrowanego. Kiedy zdefiniujemy ograniczenie unikalności to SQL Serwer stworzy unikalny indeks nieklastrowany. Więc jeżeli chcemy stworzyć indeksy na zmiennej tabelarycznej możemy to zrobić pośrednio poprzez deklarację ograniczeń. Poniższy kod prezentuje deklarację zmiennej tabelarycznej z kluczem głównym i ograniczeniem unikalności które tworzą pośrednio indeks klastrowany i nieklastrowany:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1),
UNIQUE(col2)
);

Fizyczna reprezentacja w tempdb

Istnieje błędne przekonanie, że tylko tabele tymczasowe mają reprezentację fizyczną w tempdb a zmienne tabelaryczne istnieją tylko w pamięci. To nie jest prawda. Zarówno tabele tymczasowe jak i zmienne tabelaryczne mają reprezentację fizyczną w tempdb. Można znaleźć wpisy w widoku sys.objects dla wewnętrznych tabel utworzonych w tempdb do realizacji tabel tymczasowych i zmiennych tabelarycznych. Przykładowo poniższy kod tworzy tabele tymczasową #T1 i wyszukuje w widoku sys.objects tabel zaczynających się od #:

CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
DROP TABLE #T1;
GO

Wynikiem tego zapytania jest coś podobnego do tego wyniku:

name ---------- #T1_____..._____________000000000007

Jak wspomniano wcześniej SQL Serwer wewnętrznie dodaje sufiks do nazw tabel nadanych przez użytkownika aby zapobiec konfliktom nazw w przypadku wielu sesji tworzących tabele tymczasowe. Poniższe zapytanie demonstruje deklarację zmiennej tabelarycznej i sprawdza widok sys.objects:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

Przykładowy wynik zapytania:

name ---------- #BD095663

Jak widać SQL Serwer stworzył tabelę w tempdb aby zaimplementować zadeklarowaną zmienna tabelaryczną.

Transakcje

Tabele tymczasowe i zmienne tabelaryczne zachowują się inaczej w transakcjach. Tabele tymczasowe są podobne do zwykłych tabeli. Zmiany dokonane w tabeli tymczasowej są wycofywane jeżeli transakcja jest cofnięta. Poniższy kod demonstruje taki przypadek:

CREATE TABLE #T1
(
col1 INT NOT NULL
);
BEGIN TRAN
INSERT INTO #T1(col1) VALUES(10);
ROLLBACK TRAN
SELECT col1 FROM #T1;
DROP TABLE #T1;
GO

Wynikiem działania jest pusty zbiór ponieważ transakcja została wycofana, czyli wstawianie rekordu to tabeli tymczasowej zostało cofnięte. Zmienne tabelaryczne, podobnie jak zwykłe zmienne nie zostają cofnięte jeżeli transakcja jest wycofana.Poniższy kod demonstruje taki przypadek:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
BEGIN TRAN
INSERT INTO @T1(col1) VALUES(10);
ROLLBACK TRAN
SELECT col1 FROM @T1;

Wynik:

col1 ----------- 10

Takie zachowanie zmiennych tabelarycznych jest przydatne i często wykorzystywane przy zarządzaniu błędami.

Statystyki

jeżeli chodzi o wydajność to istnieje bardzo ważna różnica między tabelami tymczasowymi i zmiennymi tabelarycznymi. SQL Serwer kolekcjonuje statystyki dla tabel tymczasowych lecz nie robi tego dla zmiennych tabelarycznych. Oznacza to, że bardziej optymalne plany zapytania uzyskamy stosując tabele tymczasowe. Odbywa się to kosztem utrzymania histogramów i re-kompilacji związanych z odświeżaniem histogramów. Poniższy przykład z istniejącym histogramem prowadzi do optymalnego planu a jego brak prowadzi do  suboptymalnego planu. Uruchom poniższy kod śledzący koszt I/O:

SET STATISTICS IO ON;

Stwórz tabelę tymczasową z milionem wierszy:

CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1),
UNIQUE(col2)
);
INSERT INTO #T1(col1, col2, col3)
SELECT n, n * 2, CAST(SYSDATETIME() AS DATE)
FROM dbo.GetNums(1, 1000000);

Kod definiuje klucz główny na kolumnie col1, powoduje to utworzenie indeksu klastrowanego. Ograniczenie unikalności na kolumnie col2 tworzy unikalany indeks nieklastrowany. Włączmy wyświetlanie aktualnego planu zapytania w SSMS za pomocą skrótu klawiszowego Ctrl + M i wykonajmy zapytanie:

SELECT col1, col2, col3
FROM #T1
WHERE col2 <= 5;

SQL Serwer stworzył następujący plan zapytania: ep111 Ten plan jest bardzo wydajny. Optymalizator zbadał histogram na col2 i oszacował, że bardzo mała liczba wierszy musi być przefiltrowana. optymalizator zdecydował o użyciu indeksu na kolumnie col2 ponieważ predykat jest bardzo selektywny. tylko niewielka liczba wyszukiwań po kluczu jest potrzebna aby dostać niezbędne wiersze. Dla tak selektywnego predykatu ten plan jest lepszy niż taki który musi przeskanować indeks klastrowany. Opcja STATISTIC IO pokazała, że tylko 9 logicznych odczytów było potrzebnych do wykonania planu. Wykonajmy podobny test ze zmienną tabelaryczną:

DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1),
UNIQUE(col2)
);
INSERT INTO @T1(col1, col2, col3)
SELECT n, n * 2, CAST(SYSDATETIME() AS DATE)
FROM dbo.GetNums(1, 1000000);
SELECT col1, col2, col3
FROM @T1
WHERE col2 <= 5;
GO

Plan zapytania: ep112 Inaczej niż w przypadku tabeli tymczasowej, SQL Serwer nie stworzył histogramu dla zmiennej tabelarycznej. Nie będąc w stanie oszacować selektywności predykatu, optymalizator oparł się na szacunkach które zakładają sztywno dość niską selektywność (30%). W wyniku czego optymalizator decyduje się na skanowanie całego indeksu klastrowanego co daje w rezultacie 2485 odczytów logicznych. Wnioskiem z tego przykładu jest to, że kiedy wydajność planu zależy od istnienia histogramów to należy używać tabel tymczasowych. Zmienne tabelaryczne są użyteczne w dwóch przypadkach. Pierwszym jest przypadek kiedy ilość danych jest bardzo mała, przykładowo strona lub dwie i wydajność planu nie jest istotna. Drugim przypadkiem jest taki, kiedy plan wykonania jest trywialny tzn kiedy optymalizator nie musi analizować histogramów do jego wyznaczenia. Przypadkiem takiego planu jest skanowanie zakresu indeksu klastrowanego lub indeksu pokrywającego. Taki plan nie jest zależny od selektywności filtra w predykacie.

Ćwiczenia

I. Porównanie ilości zamówień tegorocznych do ubiegłorocznej ilości zamówień za pomocą CTE

  1. Otwórz nowe okno SSMS i zmień kontekst na bazę danych TSQL2012.
    USE TSQL2012
    GO
    
  2. Napisz zapytanie obliczające liczbę zamówień w roku i przeanalizuj aktualny plan zapytania:
    SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
    FROM Sales.Orders
    GROUP BY YEAR(orderdate);
    
    Plan zapytania: ep113 Plan pokazuje, że skanowany jest indeks idx_nc_orderdate aby pobrać wszystkie daty zamówień a potem dane są grupowane i agregowane.
  3. Wstaw zapytanie z poprzedniego zapytania do CTE. W zewnętrznym zapytaniu połącz dwie instancje CTE tak aby porównywały bieżący rok z poprzednim i obliczały różnicę między ilościami zamówień.
    ;WITH YearlyCounts AS
    (
    SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
    FROM Sales.Orders
    GROUP BY YEAR(orderdate)
    )
    SELECT C.orderyear, C.numorders, C.numorders - P.numorders AS diff
    FROM YearlyCounts AS C
    INNER JOIN YearlyCounts AS P
    ON C.orderyear = P.orderyear + 1;
    
  4. Przeanalizuj plan wykonania zapytania z CTE. ep114 Zauważ, że skanowanie indeksu, grupowanie i agregacja zostały wykonane dwukrotnie.

II. Wykonaj to samo zadanie z użyciem zmiennej tabelarycznej.

  1. Wynik zapytania użytego w CTE wstaw do zadeklarowanej zmiennej tabelarycznej. W kolejnym zapytaniu połącz dwie instancje zmiennej tabelarycznej tak aby porównywały bieżący rok z poprzednim i obliczały różnicę między ilościami zamówień.
    DECLARE @YearlyCounts AS TABLE
    (
    orderyear INT NOT NULL,
    numorders INT NOT NULL,
    PRIMARY KEY(orderyear)
    );
    INSERT INTO @YearlyCounts(orderyear, numorders)
    SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
    FROM Sales.Orders
    GROUP BY YEAR(orderdate);
    SELECT C.orderyear, C.numorders, C.numorders - P.numorders AS diff
    FROM @YearlyCounts AS C
    INNER JOIN @YearlyCounts AS P
    ON C.orderyear = P.orderyear + 1;
    
  2. Przeanalizuj plan wykonania zapytania: ep115 Zauważ, że skanowanie, grupowanie i agregacja zostały wykonane tylko raz.