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


404

Wydaje się, że jest to obszar z kilkoma mitami i sprzecznymi poglądami.

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

+4

Podobne pytanie na temat przepełnienia stosu: [Kiedy należy użyć zmiennej tabeli vs tabeli tymczasowej w SQL Server?] (Http://stackoverflow.com/q/11857789/73226) 07 lip. 162016-07-07 04:30:51

618

Zawartość

Contents

Uwaga

Ta odpowiedź omawia „klasyczne” zmienne tabelowe wprowadzone w SQL Server 2000. SQL Server 2014 w pamięci OLTP wprowadza zoptymalizowane pod względem pamięci typy tabel.Zmienne przykłady tych tabel różnią się pod wieloma względami od omówionych poniżej!(more details).

Miejsce przechowywania

Bez różnicy.Oba są przechowywane wtempdb.

Widziałem, że sugeruje to, że w przypadku zmiennych tabel nie zawsze tak jest, ale można to zweryfikować z poniższego

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Przykładowe wyniki (pokazujące lokalizację wtempdb2 wiersze są przechowywane)

File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Lokalizacja logiczna

@table_variableszachowuj się bardziej, jakby były częścią bieżącej bazy danych#tempstoły zrobić.W przypadku zmiennych tabel (od 2005 r.) Sortowania kolumn, jeśli nie określono wprost, będą takie jak bieżącej bazy danych, natomiast dla#temptabele będą używać domyślnego sortowaniatempdb(More details).Dodatkowo typy danych zdefiniowane przez użytkownika i kolekcje XML muszą być w tempdb do użycia#temptabele, ale zmienne tabeli mogą z nich korzystać z bieżącej bazy danych (Source).

SQL Server 2012 wprowadza zawarte bazy danych.the behavior of temporary tables in these differs(h/t Aaron)

W zawartej bazie danych dane tymczasowe są zestawiane w zestawieniu zawartej bazy danych.

  • Wszystkie metadane powiązane z tabelami tymczasowymi (na przykład nazwy tabel i kolumn, indeksy itd.) Będą w sortowaniu katalogu.
  • Nazwanych ograniczeń nie można używać w tabelach tymczasowych.
  • Tabele tymczasowe mogą nie dotyczyć typów zdefiniowanych przez użytkownika, kolekcji schematów XML ani funkcji zdefiniowanych przez użytkownika.

Widoczność do różnych zakresów

@table_variablesmożna uzyskać dostęp tylko w ramach partii i zakresu, w którym są zadeklarowane.#temp_tablessą dostępne w partiach podrzędnych (zagnieżdżone wyzwalacze, procedura,execpołączenia).#temp_tablesutworzony w zewnętrznym zakresie (@@NESTLEVEL=0) może również obejmować partie, ponieważ trwają do zakończenia sesji.Żaden typ obiektu nie może być utworzony w partii podrzędnej i dostępny w zakresie wywoływania, jak omówiono dalej (globalny##tempstołymogąbądź jednak).

Dożywotni

@table_variablessą tworzone domyślnie, gdy partia zawiera aDECLARE @.. TABLEinstrukcja jest wykonywana (przed uruchomieniem dowolnego kodu użytkownika w tej partii) i jest odrzucana niejawnie na końcu.

Chociaż parser nie pozwoli ci spróbować użyć zmiennej tabeli przedDECLAREinstrukcja niejawne tworzenie można zobaczyć poniżej.

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tablessą tworzone jawnie, gdy TSQLCREATE TABLEinstrukcja została napotkana i można ją jawnie upuścićDROP TABLElub zostanie upuszczony niejawnie, gdy partia się zakończy (jeśli została utworzona w partii podrzędnej z@@NESTLEVEL > 0) lub gdy sesja kończy się inaczej.

Uwaga: W ramach przechowywanych procedur oba typy obiektówcan be cachedzamiast wielokrotnie tworzyć i upuszczać nowe tabele.Istnieją ograniczenia dotyczące tego, kiedy to buforowanie może nastąpić, mimo że możliwe jest ich naruszenie#temp_tablesale które ograniczenia@table_variablesi tak zapobiegnij.Koszty utrzymania pamięci podręcznej#tempstoły sąniecowiększy niż dla zmiennych tabelias illustrated here.

Metadane obiektów

Jest to zasadniczo takie samo dla obu typów obiektów.Jest przechowywany w tabelach podstawowych systemu wtempdb.Łatwiej jest zobaczyć#temptabela jednak jakOBJECT_ID('tempdb..#T')można użyć do wpisania do tabel systemowych, a wewnętrznie wygenerowana nazwa jest ściślej skorelowana z nazwą zdefiniowaną wCREATE TABLEkomunikat.Dla zmiennych tabeliobject_idfunkcja nie działa, a nazwa wewnętrzna jest generowana w całości bez powiązania z nazwą zmiennej.Poniższy przykład demonstruje, że metadane nadal istnieją, wprowadzając (miejmy nadzieję, unikalną) nazwę kolumny.W przypadku tabel bez unikalnych nazw kolumn obiekt_id można określić za pomocąDBCC PAGEtak długo, jak nie są puste.

/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B) 
     WITH (FILLFACTOR = 80, 
        IGNORE_DUP_KEY = ON, 
        DATA_COMPRESSION = PAGE, 
        ALLOW_ROW_LOCKS=ON, 
        ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM  tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
        ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
        ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
        ON c.object_id = p.object_id
WHERE c.name = 'dba.se'

Wydajność

Duplicate key was ignored.

    +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| object_id |  name  | rows |   type_desc   | total_pages | used_pages | data_pages | data_compression_desc |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | PAGE         |
| 574625090 | #22401542 |  13 | LOB_DATA     |     24 |     19 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | ROW_OVERFLOW_DATA |     16 |     14 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | NONE         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

Transakcje

Operacje włączone@table_variablessą przeprowadzane jako transakcje systemowe, niezależnie od jakiejkolwiek zewnętrznej transakcji użytkownika, a równoważne#tempoperacje tabelowe byłyby przeprowadzane jako część samej transakcji użytkownika.Z tego powodu aROLLBACKpolecenie wpłynie na a#tempstół, ale zostaw@table_variablenietknięty.

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Wycięcie lasu

Oba generują rekordy dziennika dotempdbdziennik transakcji.Powszechnym błędem jest to, że nie dotyczy to zmiennych tabel, więc skrypt demonstrujący to poniżej, deklaruje zmienną tabeli, dodaje kilka wierszy, a następnie aktualizuje je i usuwa.

Ponieważ zmienna tabeli jest tworzona i upuszczana domyślnie na początku i na końcu partii, konieczne jest użycie wielu partii, aby zobaczyć pełne rejestrowanie.

USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141), 
       (2, 0x41414141414141414141)

UPDATE @T
SET  B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id 
FROM  sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
        ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
        ON c.object_id = p.object_id
WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM  fn_dblog(NULL, NULL)
WHERE AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
      AS (SELECT Operation,
           Context,
           CASE
            WHEN AllocUnitId = @allocId THEN 'Table Variable'
            WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
            ELSE AllocUnitName
           END AS AllocUnitName,
           [Log Record Length]
        FROM  fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
              WHEN GROUPING(Operation) = 1 THEN 'Total'
              ELSE Operation
             END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM  T
GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER BY GROUPING(Operation),
        AllocUnitName 

Zwraca

Widok szczegółowy

Screenshot of results

Widok podsumowania (obejmuje rejestrowanie niejawnych kropli i tabel podstawowych systemu)

Screenshot of results

As far as I've been able to discernoperacje na obu generują mniej więcej równe ilości rejestrowania.

Podczas gdyIlośćrejestrowania jest bardzo podobna Jedną ważną różnicą jest to, że rekordy dziennika związane z#temptabele nie mogą być wyczyszczone, dopóki żadna zawierająca transakcja użytkownika nie zakończy się, tak długo działająca transakcja, w której w pewnym momencie zapisuje się#temptabele zapobiegną obcięciu dziennikatempdbpodczas gdy autonomiczne transakcje wywoływane dla zmiennych tabeli nie.

Zmienne tabeli nie obsługująTRUNCATEwięc może mieć wadę rejestrowania, gdy wymagane jest usunięcie wszystkich wierszy z tabeli (chociaż w przypadku bardzo małych tabelDELETEcan work out better anyway)

Kardynalność

Wiele planów wykonania zawierających zmienne tabeli pokaże pojedynczy wiersz oszacowany jako wynik z nich.Sprawdzanie właściwości zmiennych tabeli pokazuje, że SQL Server uważa, że ​​zmienna tabeli mazerowiersze (Dlaczego szacuje się, że 1 wiersz zostanie wyemitowany z tabeli wierszy zerowych jest wyjaśnione przez @ Paul Whitehere).

Jednak wyniki pokazane w poprzedniej sekcji pokazują dokładnośćrowsliczyć sięsys.partitions.Problem polega na tym, że w większości przypadków instrukcje odwołujące się do zmiennych tabeli są kompilowane, podczas gdy tabela jest pusta.Jeśli instrukcja zostanie (ponownie) skompilowana po@table_variablezostanie wypełniona, a następnie zostanie użyta dla liczności tabeli (może się to zdarzyć z powodu wyraźnegorecompilelub może dlatego, że instrukcja odwołuje się również do innego obiektu, który powoduje odroczoną kompilację lub rekompilację.)

DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

Plan pokazuje dokładną szacowaną liczbę wierszy po kompilacji odroczonej.

Shows accurate row count

W dodatku SP2 dla programu SQL Server 2012 wprowadzono flagę śledzenia 2453.Więcej szczegółów znajduje się w „Silniku relacyjnym”here.

Gdy ta flaga śledzenia jest włączona, może to spowodować automatyczną ponowną kompilację, aby uwzględnić zmienioną liczność, jak omówiono dalej bardzo krótko.

Brak statystyk kolumn

Posiadanie dokładniejszej liczności tabeli nie oznacza jednak, że szacowana liczba wierszy będzie bardziej dokładna (chyba że wykona operację na wszystkich wierszach tabeli).SQL Server w ogóle nie prowadzi statystyk kolumn dla zmiennych tabeli, więc bazuje na domysłach na podstawie predykatu porównania (np. 10% tabeli zostanie zwrócone dla=przeciwko nieunikalnej kolumnie lub 30% dla a>porównanie).Statystyki kolumn w kontraścieutrzymany dla#tempstoły.

SQL Server utrzymuje liczbę modyfikacji dokonanych w każdej kolumnie.Jeśli liczba modyfikacji od czasu skompilowania planu przekracza próg rekompilacji (RT), plan zostanie ponownie skompilowany, a statystyki zaktualizowane.RT zależy od typu i rozmiaru tabeli.

ZPlan Caching in SQL Server 2008

RT oblicza się w następujący sposób.(n odnosi się do liczności tabeli podczas kompilowania planu kwerend).

Stały stół
- Jeśli n <= 500, RT = 500.
- Jeśli n> 500, RT = 500 + 0,20 * n.

Tabela tymczasowa
- Jeśli n <6, RT = 6.
- Jeśli 6 <= n <= 500, RT = 500.
- Jeśli n> 500, RT = 500 + 0,20 * n.
Zmienna tabeli
- RT nie istnieje.Dlatego rekompilacje nie zdarzają się ze względu na zmiany liczności zmiennych tabeli.(Ale patrz uwaga o TF 2453 poniżej)

KEEP PLANwskazówka może być użyta do ustawienia RT dla#tempstoły takie same jak przy stołach stałych.

Efektem netto tego wszystkiego są często generowane plany wykonania#temptabele są rzędami wielkości lepszymi niż dla@table_variablesgdy zaangażowanych jest wiele wierszy, ponieważ SQL Server ma lepsze informacje do pracy.

NB1: Zmienne tabeli nie mają statystyk, ale wciąż mogą wywołać zdarzenie rekompilacji „Statystyka zmieniona” pod flagą śledzenia 2453 (nie dotyczy „trywialnych” planów). Wydaje się, że występują one pod tymi samymi progami rekompilacji, jak pokazano dla tabel tymczasowych powyżej z dodatkowy, który jeśliN=0 -> RT = 1.tzn. wszystkie instrukcje kompilowane, gdy zmienna tabeli jest pusta, kończą się ponowną kompilacją i poprawąTableCardinalitypo raz pierwszy są wykonywane, gdy nie są puste.Liczność tabeli kompilacji jest przechowywana w planie i jeśli instrukcja jest wykonywana ponownie z tą samą licznością (z powodu przepływu instrukcji sterujących lub ponownego użycia planu pamięci podręcznej), nie występuje rekompilacja.

NB2: W przypadku buforowanych tabel tymczasowych w procedurach przechowywanych historia kompilacji jest znacznie bardziej skomplikowana niż opisana powyżej.WidziećTemporary Tables in Stored Proceduresdla wszystkich drobnych szczegółów.

Ponownie kompiluje

Jak również rekompilacje oparte na modyfikacji opisane powyżej#temptabele mogą być również powiązane zadditional compilespo prostu dlatego, że zezwalają na operacje zabronione dla zmiennych tabel, które wyzwalają kompilację (np. zmiany DDLCREATE INDEX,ALTER TABLE)

Zamykający

Tohas been statedże zmienne tabeli nie uczestniczą w blokowaniu.Nie o to chodzi.Uruchamianie poniższych wyników do zakładki wiadomości SSMS zawiera szczegółowe informacje o blokadach wydanych i wydanych dla instrukcji wstawiania.

DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

W przypadku zapytańSELECTze zmiennych tabeli Paul White wskazuje w komentarzach, że automatycznie pojawiają się one w sposób ukrytyNOLOCKWskazówka.Jest to pokazane poniżej

DECLARE @T TABLE(X INT); 

SELECT X
FROM @T 
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

Wydajność

*** Output Tree: (trivial plan) ***

       PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )

Wpływ tego na blokowanie może być jednak niewielki.

SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))


DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Żaden z tych wyników nie zwraca kolejności klucza indeksu wskazującego, że SQL Server używałallocation ordered scandla obu.

Wykonałem powyższy skrypt dwa razy, a wyniki drugiego uruchomienia są poniżej

Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0 

Wyjście blokujące dla zmiennej tabeli jest rzeczywiście bardzo minimalne, ponieważ SQL Server po prostu uzyskuje blokadę stabilności schematu na obiekcie.Ale na#tempstół jest prawie tak lekki, że usuwa poziom obiektuSzamek.ZANOLOCKpodpowiedź lubREAD UNCOMMITTEDpoziom izolacji można oczywiście określić wyraźnie podczas pracy#temprównież stoły.

Podobnie jak w przypadku rejestrowania, otaczająca transakcja użytkownika może oznaczać, że blokady są przechowywane dłużej#tempstoły.Z poniższym skryptem

  --BEGIN TRAN;  

     CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

     INSERT INTO #T (X) VALUES(1) 

     SELECT CASE resource_type
          WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
          WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                         FROM tempdb.sys.allocation_units a 
                         JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                         WHERE a.allocation_unit_id = resource_associated_entity_id)
          WHEN 'DATABASE' THEN DB_NAME(resource_database_id)                   
          ELSE (SELECT OBJECT_NAME(object_id, 2)
             FROM  tempdb.sys.partitions
             WHERE partition_id = resource_associated_entity_id)
         END AS object_name,
         *
     FROM  sys.dm_tran_locks
     WHERE request_session_id = @@SPID

     DROP TABLE #T

     -- ROLLBACK 

gdy uruchamiany poza jawną transakcją użytkownika dla obu przypadków, jedyny zwrócony blokada podczas sprawdzaniasys.dm_tran_locksjest wspólną blokadąDATABASE.

Po odkomentowaniuBEGIN TRAN ... ROLLBACKZwracanych jest 26 wierszy pokazujących, że blokady są przechowywane zarówno na samym obiekcie, jak i na wierszach tabeli systemowej, aby umożliwić wycofanie i uniemożliwić innym transakcjom odczytywanie niezatwierdzonych danych.Operacja równoważnej zmiennej tabeli nie podlega wycofaniu z transakcją użytkownika i nie ma potrzeby utrzymywania tych blokad, abyśmy mogli sprawdzić w następnej instrukcji, ale śledzenie blokad nabytych i zwolnionych w Profiler lub przy użyciu flagi śledzenia 1200 pokazuje, że wciąż jest wiele zdarzeń blokowania pojawić się.

Indeksy

Dla wersji wcześniejszych niż SQL Server 2014 indeksy mogą być tworzone niejawnie tylko na zmiennych tabeli jako efekt uboczny dodawania ograniczenia unikalnego lub klucza podstawowego.To oczywiście oznacza, że ​​obsługiwane są tylko unikalne indeksy.Nieunikalny indeks nieklastrowany na tabeli z unikalnym indeksem klastrowym można jednak symulować, po prostu deklarując goUNIQUE NONCLUSTEREDi dodanie klucza CI na końcu żądanego klucza NCI (SQL Server takdo this behind the scenes anywaynawet jeśli można określić nieunikalny NCI)

Jak wykazano wcześniej, różneindex_options można określić w deklaracji ograniczenia, w tymDATA_COMPRESSION,IGNORE_DUP_KEY, iFILLFACTOR(chociaż nie ma sensu ustawiać tego, ponieważ tylko zmieniałoby to indeks i nie można odbudować indeksów zmiennych tabeli!)

Dodatkowo zmienne tabeli nie obsługująINCLUDEd kolumny, filtrowane indeksy (do 2016 r.) lub partycjonowanie,#temptabele robią (schemat partycji musi zostać utworzony wtempdb).

Indeksy w SQL Server 2014

Nieunikalne indeksy można zadeklarować inline w definicji zmiennej tabeli w SQL Server 2014. Przykładowa składnia tego jest poniżej.

DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Indeksy w SQL Server 2016

Z CTP 3.1 można teraz deklarować filtrowane indeksy dla zmiennych tabeli.Przez RTM tomożew takim przypadku dozwolone jest również dołączanie kolumnwill likely not make it into SQL16 due to resource constraints

DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

Równoległość

Zapytania wstawiane (lub w inny sposób modyfikowane)@table_variablesnie może mieć planu równoległego,#temp_tablesnie są ograniczone w ten sposób.

Widoczne jest obejście tego problemu, ponieważ przepisywanie w następujący sposób pozwala naSELECTczęść odbywa się równolegle, ale kończy się przy użyciu ukrytego stołu tymczasowego(behind the scenes)

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

Nie ma takiego ograniczenia w zapytaniachWybierzze zmiennych tabelias illustrated in my answer here

Inne różnice funkcjonalne

  • #temp_tablesnie można używać wewnątrz funkcji.@table_variablesmoże być używany wewnątrz skalarnych lub wielozadaniowych tabel UDF.
  • @table_variablesnie może mieć nazwanych ograniczeń.
  • @table_variablesnie może byćSELECT-edINTO,ALTER-ed,TRUNCATEd lub być celemDBCCpolecenia takie jakDBCC CHECKIDENTlub zSET IDENTITY INSERTi nie obsługują wskazówek dotyczących tabel, takich jakWITH (FORCESCAN)
  • CHECKograniczenia dotyczące zmiennych tabeli nie są uwzględniane przez optymalizator dla uproszczenia, predykatów implikowanych lub wykrywania sprzeczności.
  • Zmienne tabeli wydają się nie kwalifikować dorowset sharing optimisationOznacza to, że usuwanie i aktualizowanie planów przeciwko tym może spotkać się z większym obciążeniem iPAGELATCH_EXczeka.(Example)

Tylko pamięć?

Jak stwierdzono na początku, oba są przechowywane na stronachtempdb.Jednak nie mówiłem, czy istnieje jakakolwiek różnica w zachowaniu, jeśli chodzi o zapisywanie tych stron na dysku.

Przeprowadziłem teraz niewielką ilość testów i jak dotąd nie widziałem takiej różnicy.W konkretnym teście przeprowadzonym na moim wystąpieniu SQL Server 250 stron wydaje się punktem odcięcia, zanim plik danych zostanie zapisany.

Uwaga: Poniższe zachowanie nie występuje już w SQL Server 2014 lubSQL Server 2012 SP1/CU10 or SP2/CU1chętny pisarz nie jest już tak chętny do pisania stron na dysk.Więcej szczegółów na temat tej zmiany na stronieSQL Server 2014: tempdb Hidden Performance Gem.

Uruchamianie poniższego skryptu

CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

I monitorowanie pisze dotempdbplik danych z Process Monitor Nie widziałem żadnego (z wyjątkiem sporadycznie tych do strony startowej bazy danych z przesunięciem 73.728).Po zmianie250do251Zacząłem widzieć napisy jak poniżej.

ProcMon

Powyższy zrzut ekranu pokazuje zapisy stron 5 * 32, a jedna strona zapisuje, że 161 stron zostało zapisanych na dysku.Otrzymałem taki sam punkt odcięcia 250 stron podczas testowania ze zmiennymi tabeli.Poniższy skrypt pokazuje to inaczej, patrząc nasys.dm_os_buffer_descriptors

DECLARE @T TABLE (
    X    INT,
    [dba.se] CHAR(8000) NULL)

INSERT INTO @T
         (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM  master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                    FROM  tempdb.sys.partitions AS p
                   INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                        ON p.hobt_id = a.container_id
                       INNER JOIN tempdb.sys.columns AS c
                        ON c.object_id = p.object_id
                    WHERE c.name = 'dba.se')
GROUP BY is_modified 

Wyniki

is_modified page_count
----------- -----------
0      192
1      61

Pokazując, że 192 strony zostały zapisane na dysku, a brudna flaga wyczyszczona.Pokazuje również, że zapis na dysk nie oznacza, że ​​strony zostaną natychmiast usunięte z puli buforów.Zapytania dotyczące tej zmiennej tabeli nadal mogą być w pełni spełnione z pamięci.

Na bezczynnym serwerze zmax server memoryUstawić2000 MBiDBCC MEMORYSTATUSraportowanie stron puli buforów przydzielonych jako około 1 843 000 KB (około 23 000 stron) Do tabel powyżej wstawiłem partie po 1000 wierszy/stron i dla każdej nagranej iteracji.

SELECT Count(*)
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE' 

Zarówno zmienna tabeli, jak i#temptabela dała prawie identyczne wykresy i zdołała maksymalnie wydłużyć pulę buforów, zanim dotarła do punktu, w którym nie były one całkowicie przechowywane w pamięci, więc nie wydaje się, aby istniało jakieś szczególne ograniczenie, ile pamięci może zużyć.

Pages in Buffer Pool


35

Jest kilka rzeczy, na które chciałbym zwrócić uwagę bardziej na podstawie konkretnych doświadczeń niż badań.Jako DBA jestem bardzo nowy, więc proszę, popraw mnie, jeśli to konieczne.

  1. Tabele #temp domyślnie używają domyślnego sortowania instancji SQL Server.Jeśli nie określono inaczej, możesz napotkać problemy podczas porównywania lub aktualizowania wartości między tabelami #temp i tabelami bazy danych, jeśli masterdb ma inną sortowanie od bazy danych.Patrz: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
  2. Całkowicie oparta na osobistym doświadczeniu dostępna pamięć wydaje się mieć wpływ, który działa lepiej.MSDN zaleca używanie zmiennych tabel do przechowywania mniejszych zestawów wyników, ale przez większość czasu różnica nie jest nawet zauważalna.W większych zestawach, w niektórych przypadkach staje się oczywiste, że zmienne tabel są znacznie bardziej obciążające pamięć i mogą spowolnić zapytanie do przeszukiwania.
+5

Zauważ również, że sortowanie w tabelach #temp * może * dziedziczyć sortowanie bazy danych wywołujących, jeśli używasz SQL Server 2012 i baza danych jest zawarta. 29 sty. 132013-01-29 19:51:03

  0

Wyjaśnienie # 2 dla małych i dużych zestawów http://stackoverflow.com/a/14465163/5224021 08 lut. 172017-02-08 14:56:07