Jaka jest różnica między tabelą tymczasową a zmienną tabelową w programie SQL Server?

W SQL Server 2005 możemy tworzyć tabele tymczasowe na jeden z dwóch sposobów:

declare @tmp table (Col1 int, Col2 int);

lub

create table #tmp (Col1 int, Col2 int);

Jakie są różnice między tymi dwoma? Czytałem sprzeczne opinie na temat tego, czy @tmp nadal używa tempdb, czy też wszystko dzieje się w pamięci.

W jakich scenariuszach jedno wyprzedza drugie?

Istnieje kilka różnic między tabelami tymczasowymi (#tmp) a zmiennymi tabel (@tmp), chociaż użycie tempdb nie jest jedną z nich, jak podano w linku MSDN poniżej.

Zasadniczo w przypadku małych i średnich ilości danych oraz prostych scenariuszy użycia należy używać zmiennych tabel. (Jest to zbyt szeroka wskazówka, oczywiście z wieloma wyjątkami - patrz poniżej i następujące artykuły).

Kilka punktów do rozważenia przy wyborze między nimi:

• Tabele tymczasowe są prawdziwymi tabelami, więc możesz wykonywać takie czynności, jak TWORZENIE INDEKSÓW itp. Jeśli masz duże ilości danych, dla których dostęp do indeksu będzie szybszy, dobrym rozwiązaniem są tabele tymczasowe.

• Zmienne tabelowe mogą mieć indeksy przy użyciu ograniczeń PRIMARY KEY lub UNIQUE. (Jeśli chcesz mieć nieunikalny indeks, po prostu uwzględnij kolumnę klucza podstawowego jako ostatnią kolumnę w unikalnym ograniczeniu. Jeśli nie masz unikalnej kolumny, możesz użyć kolumny tożsamości). SQL 2014 ma również nieunikalne indeksy .

• Zmienne tabelowe nie uczestniczą w transakcjach i SELECTsą domyślnie z nimi związane NOLOCK. Zachowanie transakcyjne może być bardzo pomocne, na przykład jeśli chcesz ODWRÓCIĆ w połowie procedury, wówczas zmienne tabeli wypełnione podczas tej transakcji będą nadal zapełniane!

• Tabele tymczasowe mogą powodować kompilację procedur przechowywanych, być może często. Zmienne tabeli nie będą.

• Możesz utworzyć tabelę tymczasową za pomocą SELECT INTO, która może być szybsza do napisania (dobra w przypadku zapytań ad-hoc) i może pozwolić ci radzić sobie ze zmieniającymi się typami danych w czasie, ponieważ nie musisz z góry definiować struktury tabeli tymczasowej.

• Możesz przekazać zmienne tabeli z powrotem z funkcji, co pozwala znacznie uprościć i ponownie wykorzystać logikę (np. Uczynić funkcję dzielącą ciąg znaków na tabelę wartości na dowolnym ograniczniku).

• Korzystanie ze zmiennych tabel w ramach funkcji zdefiniowanych przez użytkownika umożliwia szersze korzystanie z tych funkcji (szczegółowe informacje zawiera dokumentacja TWORZENIE FUNKCJI). Jeśli piszesz funkcję, powinieneś używać zmiennych tabel zamiast tabel tymczasowych, chyba że istnieje wyraźna potrzeba inaczej.

• Zarówno zmienne tabel, jak i tabele tymczasowe są przechowywane w tempdb. Jednak zmienne tabel (od 2005 r.) Domyślnie zestawiają bieżącą bazę danych z tabelami temp, które przyjmują domyślne zestawienie tempdb ( ref ). Oznacza to, że powinieneś zdawać sobie sprawę z problemów z sortowaniem, jeśli używasz tabel tymczasowych, a sortowanie bazy danych różni się od tempdb, powodując problemy, jeśli chcesz porównać dane w tabeli tymczasowej z danymi w bazie danych.

• Globalne tabele temperatur (## tmp) to inny rodzaj tabeli tymczasowej dostępnej dla wszystkich sesji i użytkowników.

Dalsza lektura:

• Świetna odpowiedź Martina Smitha na dba.stackexchange.com

• MSDN FAQ na temat różnic między nimi: https://support.microsoft.com/en-gb/kb/305977

• Artykuł na blogu MDSN: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

• Artykuł: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

• Nieoczekiwane zachowania i wpływ na wydajność tabel tymczasowych i zmiennych tymczasowych: Paul White na SQLblog.com

Wystarczy spojrzeć na twierdzenie w zaakceptowanej odpowiedzi, że zmienne tabeli nie uczestniczą w logowaniu.

Wydaje się, ogólnie rzecz biorąc, nieprawdziwe, że istnieje jakakolwiek różnica w ilości rejestrowania (przynajmniej dla operacji insert/ update/ deletedo samej tabeli, chociaż od tego czasu odkryłem, że istnieje niewielka różnica w tym zakresie dla buforowanych obiektów tymczasowych w procedurach przechowywanych z powodu dodatkowej tabeli systemowej aktualizacje).

Patrzyłem na zachowanie rejestrowania zarówno dla tabeli @table_variablea, jak i #tempdla następujących operacji.

1. Udane wstawienie
2. Wstawianie wielu wierszy, w którym wycofano instrukcję z powodu naruszenia ograniczenia.
3. Aktualizacja
4. Usunąć
5. Cofnij przydział

Rekordy dziennika transakcji były prawie identyczne dla wszystkich operacji.

Wersja zmiennej tabeli faktycznie ma kilka dodatkowych wpisów w dzienniku, ponieważ zostaje dodana do sys.syssingleobjrefstabeli podstawowej (a później usunięta z niej), ale ogólnie zarejestrowano kilka mniej bajtów, ponieważ wewnętrzna nazwa zmiennych tabeli zużywa 236 bajtów mniej niż #temptabel (118 mniejnvarchar znaków ).

Pełny skrypt do odtworzenia (najlepiej uruchomić na instancji uruchomionej w trybie pojedynczego użytkownika i w sqlcmdtrybie)

:setvar tablename "@T" 
:setvar tablescript "DECLARE @T TABLE"

/*
 --Uncomment this section to test a #temp table
:setvar tablename "#T" 
:setvar tablescript "CREATE TABLE #T"
*/

USE tempdb 
GO    
CHECKPOINT

DECLARE @LSN NVARCHAR(25)

SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null) 


EXEC(N'BEGIN TRAN StartBatch
SAVE TRAN StartBatch
COMMIT

$(tablescript)
(
[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,
InRowFiller char(7000) DEFAULT ''A'',
OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000)
)


BEGIN TRAN InsertFirstRow
SAVE TRAN InsertFirstRow
COMMIT

INSERT INTO $(tablename)
DEFAULT VALUES

BEGIN TRAN Insert9Rows
SAVE TRAN Insert9Rows
COMMIT


INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns

BEGIN TRAN InsertFailure
SAVE TRAN InsertFailure
COMMIT


/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/
BEGIN TRY
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20
FROM sys.all_columns
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH

BEGIN TRAN Update10Rows
SAVE TRAN Update10Rows
COMMIT

UPDATE $(tablename)
SET InRowFiller = LOWER(InRowFiller),
    OffRowFiller  =LOWER(OffRowFiller),
    LOBFiller  =LOWER(LOBFiller)


BEGIN TRAN Delete10Rows
SAVE TRAN Delete10Rows
COMMIT

DELETE FROM  $(tablename)
BEGIN TRAN AfterDelete
SAVE TRAN AfterDelete
COMMIT

BEGIN TRAN EndBatch
SAVE TRAN EndBatch
COMMIT')


DECLARE @LSN_HEX NVARCHAR(25) = 
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)        

SELECT 
    [Operation],
    [Context],
    [AllocUnitName],
    [Transaction Name],
    [Description]
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  

SELECT CASE
         WHEN GROUPING(Operation) = 1 THEN 'Total'
         ELSE Operation
       END AS Operation,
       Context,
       AllocUnitName,
       COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
       COUNT(*)                              AS Cnt
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  
GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())

Wyniki

+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
|                       |                    |                           |             @TV      |             #TV      |                  |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Operation             | Context            | AllocUnitName             | Size in Bytes | Cnt  | Size in Bytes | Cnt  | Difference Bytes |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| LOP_ABORT_XACT        | LCX_NULL           |                           | 52            | 1    | 52            | 1    |                  |
| LOP_BEGIN_XACT        | LCX_NULL           |                           | 6056          | 50   | 6056          | 50   |                  |
| LOP_COMMIT_XACT       | LCX_NULL           |                           | 2548          | 49   | 2548          | 49   |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 624           | 3    | 624           | 3    |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 208           | 1    | 208           | 1    |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrscols.clst        | 832           | 4    | 832           | 4    |                  |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL           |                           | 120           | 3    | 120           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 720           | 9    | 720           | 9    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.clust   | 444           | 3    | 444           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.nc      | 276           | 3    | 276           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.clst       | 628           | 4    | 628           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.nc         | 484           | 4    | 484           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.clst      | 176           | 1    | 176           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.nc        | 144           | 1    | 144           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.clst        | 100           | 1    | 100           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.nc1         | 88            | 1    | 88            | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysobjvalues.clst     | 596           | 5    | 596           | 5    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrowsets.clust      | 132           | 1    | 132           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrscols.clst        | 528           | 4    | 528           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.clst       | 1040          | 6    | 1276          | 6    | 236              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc1        | 820           | 6    | 1060          | 6    | 240              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc2        | 820           | 6    | 1060          | 6    | 240              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc3        | 480           | 6    | 480           | 6    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.clst | 96            | 1    |               |      | -96              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.nc1  | 88            | 1    |               |      | -88              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | Unknown Alloc Unit        | 72092         | 19   | 72092         | 19   |                  |
| LOP_DELETE_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 16348         | 37   | 16348         | 37   |                  |
| LOP_FORMAT_PAGE       | LCX_HEAP           | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |
| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit        | 252           | 3    | 252           | 3    |                  |
| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 84            | 1    | 84            | 1    |                  |
| LOP_FORMAT_PAGE       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 4788          | 57   | 4788          | 57   |                  |
| LOP_HOBT_DDL          | LCX_NULL           |                           | 108           | 3    | 108           | 3    |                  |
| LOP_HOBT_DELTA        | LCX_NULL           |                           | 9600          | 150  | 9600          | 150  |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 456           | 3    | 456           | 3    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syscolpars.clst       | 644           | 4    | 644           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysidxstats.clst      | 180           | 1    | 180           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysiscols.clst        | 104           | 1    | 104           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysobjvalues.clst     | 616           | 5    | 616           | 5    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 136           | 1    | 136           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrscols.clst        | 544           | 4    | 544           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1064          | 6    | 1300          | 6    | 236              |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syssingleobjrefs.clst | 100           | 1    |               |      | -100             |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | Unknown Alloc Unit        | 135888        | 19   | 135888        | 19   |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysallocunits.nc      | 288           | 3    | 288           | 3    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syscolpars.nc         | 500           | 4    | 500           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysidxstats.nc        | 148           | 1    | 148           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysiscols.nc1         | 92            | 1    | 92            | 1    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc1        | 844           | 6    | 1084          | 6    | 240              |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc2        | 844           | 6    | 1084          | 6    | 240              |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc3        | 504           | 6    | 504           | 6    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syssingleobjrefs.nc1  | 92            | 1    |               |      | -92              |
| LOP_INSERT_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 5112          | 71   | 5112          | 71   |                  |
| LOP_MARK_SAVEPOINT    | LCX_NULL           |                           | 508           | 8    | 508           | 8    |                  |
| LOP_MODIFY_COLUMNS    | LCX_CLUSTERED      | Unknown Alloc Unit        | 1560          | 10   | 1560          | 10   |                  |
| LOP_MODIFY_HEADER     | LCX_HEAP           | Unknown Alloc Unit        | 3780          | 45   | 3780          | 45   |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.syscolpars.clst       | 384           | 4    | 384           | 4    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysidxstats.clst      | 100           | 1    | 100           | 1    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysrowsets.clust      | 92            | 1    | 92            | 1    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1144          | 13   | 1144          | 13   |                  |
| LOP_MODIFY_ROW        | LCX_IAM            | Unknown Alloc Unit        | 4224          | 48   | 4224          | 48   |                  |
| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit        | 13632         | 169  | 13632         | 169  |                  |
| LOP_MODIFY_ROW        | LCX_TEXT_MIX       | Unknown Alloc Unit        | 108640        | 120  | 108640        | 120  |                  |
| LOP_ROOT_CHANGE       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 960           | 10   | 960           | 10   |                  |
| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit        | 1200          | 20   | 1200          | 20   |                  |
| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit        | 1080          | 18   | 1080          | 18   |                  |
| LOP_SET_BITS          | LCX_SGAM           | Unknown Alloc Unit        | 120           | 2    | 120           | 2    |                  |
| LOP_SHRINK_NOOP       | LCX_NULL           |                           |               |      | 32            | 1    | 32               |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Total                 |                    |                           | 410144        | 1095 | 411232        | 1092 | 1088             |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+

W jakich scenariuszach jedno wyprzedza drugie?

W przypadku mniejszych tabel (mniej niż 1000 wierszy) użyj zmiennej temp, w przeciwnym razie użyj tabeli temp.

@wcm - tak naprawdę, aby wybrać Tabela Zmienna nie jest tylko Ram - może być częściowo zapisana na dysku.

Tabela temp może mieć indeksy, podczas gdy zmienna tabeli może mieć tylko indeks podstawowy. Jeśli problemem jest prędkość Zmienne tabeli mogą być szybsze, ale oczywiście, jeśli istnieje wiele rekordów lub potrzeba przeszukiwania tabeli tymczasowej indeksu klastrowego, wtedy Tabela Temp byłaby lepsza.

Dobry artykuł w tle

1. Tabela temperatur: Tabela temperatur jest łatwa do tworzenia i tworzenia kopii zapasowych danych.

   Zmienna tabeli: Ale zmienna tabeli wymaga wysiłku, gdy zwykle tworzymy normalne tabele.

2. Tabela temperatur: wynik tabeli temperatur może być używany przez wielu użytkowników.

   Zmienna tabeli: Ale zmienna tabeli może być używana tylko przez bieżącego użytkownika. 

3. Tabela temperatur: Tabela temperatur będzie przechowywana w tempdb. To spowoduje ruch w sieci. Gdy mamy duże dane w tabeli tymczasowej, musi ona działać w całej bazie danych. Wystąpi problem z wydajnością.

   Zmienna tabeli: Ale zmienna tabeli będzie przechowywać w pamięci fizycznej niektóre dane, a następnie, gdy rozmiar wzrośnie, zostanie przeniesiona do tempdb.

4. Tabela temperatur: Tabela temperatur może wykonywać wszystkie operacje DDL. Pozwala tworzyć indeksy, upuszczać, zmieniać itp.,

   Zmienna tabeli: Natomiast zmienna tabeli nie pozwala na wykonywanie operacji DDL. Ale zmienna tabelowa pozwala nam tworzyć tylko indeks klastrowany.

5. Tabela temperatur: Tabela temperatur może być używana dla bieżącej sesji lub globalnie. Aby sesja wielu użytkowników mogła wykorzystać wyniki z tabeli.

   Zmienna tabeli: ale zmienna tabeli może być używana do tego programu. (Procedura składowana)

6. Tabela temp: Zmienna Temp nie może korzystać z transakcji. Kiedy wykonujemy operacje DML za pomocą tabeli tymczasowej, wówczas można ją wycofać lub zatwierdzić transakcje.

   Zmienna tabeli: Ale nie możemy tego zrobić dla zmiennej tabeli.

7. Tabela temperatur: Funkcje nie mogą używać zmiennej temp. Co więcej, nie możemy wykonać operacji DML w funkcjach.

   Zmienna tabeli: Ale funkcja pozwala nam użyć zmiennej tabeli. Ale używając zmiennej tabeli możemy to zrobić.

8. Tabela temp: procedura przechowywana dokona ponownej kompilacji (nie można użyć tego samego planu wykonania), gdy użyjemy zmiennej temp dla każdego kolejnego wywołania.

   Zmienna tabeli: podczas gdy zmienna tabeli tak się nie robi.

Dla wszystkich, którzy wierzą w mit, że zmienne temp są tylko w pamięci

Po pierwsze, zmienna tabelowa niekoniecznie musi rezydować w pamięci. Pod presją pamięci strony należące do zmiennej tabeli można wypchnąć do tempdb.

Przeczytaj artykuł tutaj: TempDB :: Zmienna tabeli a lokalna tabela tymczasowa

Inną główną różnicą jest to, że zmienne tabelowe nie mają statystyk kolumnowych, podobnie jak tabele tymczasowe. Oznacza to, że optymalizator zapytań nie wie, ile wierszy znajduje się w zmiennej tabeli (zgaduje 1), co może prowadzić do wygenerowania wysoce nieoptymalnych planów, jeśli zmienna tabeli faktycznie ma dużą liczbę wierszy.

Cytat pochodzi z; Profesjonalne składniki wewnętrzne programu SQL Server 2012 i rozwiązywanie problemów

Statystyka Główną różnicą między tabelami tymczasowymi a zmiennymi tabelowymi jest to, że statystyki nie są tworzone na zmiennych tabelowych. Ma to dwie główne konsekwencje, z których pierwszą jest to, że Optymalizator Kwerend wykorzystuje ustalone oszacowanie liczby wierszy w zmiennej tabeli, niezależnie od zawartych w niej danych. Ponadto dodawanie lub usuwanie danych nie zmienia oszacowania.

Indeksy Nie można tworzyć indeksów na zmiennych tabeli, chociaż można tworzyć ograniczenia. Oznacza to, że tworząc klucze podstawowe lub unikalne ograniczenia, możesz mieć indeksy (ponieważ są one tworzone w celu obsługi ograniczeń) zmiennych tabeli. Nawet jeśli masz ograniczenia, a zatem indeksy, które będą miały statystyki, indeksy nie będą używane podczas kompilacji zapytania, ponieważ nie będą istnieć w czasie kompilacji, ani nie spowodują ponownej kompilacji.

Modyfikacje schematu Modyfikacje schematu są możliwe w tabelach tymczasowych, ale nie w zmiennych tabeli. Chociaż modyfikacje schematu są możliwe w tabelach tymczasowych, należy ich unikać, ponieważ powodują one rekompilacje instrukcji korzystających z tabel.

ZMIENNE TABELI NIE SĄ TWORZONE W PAMIĘCI

Istnieje powszechne błędne przekonanie, że zmienne tabel są strukturami w pamięci i jako takie będą działać szybciej niż tabele tymczasowe . Dzięki DMV o nazwie sys. dm _ db _ sesja _ przestrzeń _ użycie, które pokazuje użycie tempdb według sesji, możesz udowodnić, że tak nie jest . Po ponownym uruchomieniu programu SQL Server w celu wyczyszczenia DMV, uruchom następujący skrypt, aby potwierdzić, że twoja sesja _ id zwraca 0 dla użytkownika _ obiektów _ przydziel _ _ strona _ liczba:

SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Teraz możesz sprawdzić, ile miejsca zajmuje tymczasowa tabela, uruchamiając następujący skrypt, aby utworzyć tymczasową tabelę z jedną kolumną i wypełnić ją jednym wierszem:

CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Wyniki na moim serwerze wskazują, że do tabeli przypisano jedną stronę w tempdb. Teraz uruchom ten sam skrypt, ale tym razem użyj zmiennej tabeli:

DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Którego użyć?

To, czy użyjesz tabel tymczasowych, czy zmiennych tabeli, powinno zostać ustalone na podstawie dokładnych testów, ale najlepiej jest wybrać domyślne tabele tymczasowe, ponieważ jest o wiele mniej rzeczy, które mogą pójść nie tak .

Widziałem, jak klienci opracowują kod przy użyciu zmiennych tabeli, ponieważ mieli do czynienia z małą liczbą wierszy, i było to szybsze niż tabela tymczasowa, ale kilka lat później w zmiennej tabeli były setki tysięcy wierszy, a wydajność była straszna , więc podejmując decyzję, pozwól sobie na pewne planowanie pojemności!

Kolejna różnica:

Dostęp do tabeli var można uzyskać tylko z instrukcji w ramach procedury, która ją tworzy, a nie z innych procedur wywoływanych przez tę procedurę lub zagnieżdżonego dynamicznego SQL (przez exec lub sp_executesql).

Z drugiej strony zakres tabeli tymczasowej obejmuje kod w wywoływanych procedurach i zagnieżdżony dynamiczny SQL.

Jeśli tabela utworzona przez twoją procedurę musi być dostępna z innych wywoływanych procedur lub dynamicznego SQL, musisz użyć tabeli tymczasowej. Może to być bardzo przydatne w skomplikowanych sytuacjach.

Różnice między Temporary Tables (##temp/#temp)i Table Variables (@table)są następujące:

1. Table variable (@table)jest tworzony w memory. Natomiast a Temporary table (##temp/#temp)jest tworzony w tempdb database. Jeśli jednak występuje presja pamięci, strony należące do zmiennej tabeli mogą zostać wypchnięte do tempdb.

2. Table variablesnie może być zaangażowany transactions, logging or locking. To sprawia, że @table faster then #temp. Tak więc zmienna tabeli jest szybsza niż tabela tymczasowa.

3. Temporary tablepozwala na modyfikacje schematu w przeciwieństwie do Table variables.

4. Temporary tablessą widoczne w utworzonej procedurze, a także w procedurach potomnych. Natomiast zmienne tabeli są widoczne tylko w utworzonej procedurze.

5. Temporary tablessą dozwolone CREATE INDEXes, Table variablesale nie są dozwolone, CREATE INDEXzamiast tego mogą mieć indeks za pomocą Primary Key or Unique Constraint.

Weź również pod uwagę, że często możesz zastąpić obie tabelami pochodnymi, które również mogą być szybsze. Jednak, podobnie jak w przypadku dostrajania wydajności, tylko rzeczywiste testy na rzeczywistych danych mogą wskazać najlepsze podejście do konkretnego zapytania.

Zaskakuje mnie, że nikt nie wspomniał o kluczowej różnicy między tymi dwoma, że ​​tabela temp obsługuje wstawianie równoległe, podczas gdy zmienna tabeli nie. Powinieneś być w stanie zobaczyć różnicę w stosunku do planu wykonania. A oto wideo z warsztatów SQL na kanale 9 .

To wyjaśnia również, dlaczego powinieneś używać zmiennej temp dla mniejszych tabel, w przeciwnym razie użyj tabeli temp, jak wcześniej odpowiedział SQLMenace .