In SQL Server 2005 können wir temporäre Tabellen auf zwei Arten erstellen:
declare @tmp table (Col1 int, Col2 int);o
create table #tmp (Col1 int, Col2 int);Was sind die Unterschiede zwischen diesen beiden? Ich habe widersprüchliche Meinungen darüber gelesen, ob @tmp noch tempdb verwendet, oder ob alles im Speicher passiert.
In welchen Szenarien ist das eine besser als das andere?
Ich habe mir gerade die Behauptung in der akzeptierten Antwort angesehen, dass Tabellenvariablen nicht an der Protokollierung teilnehmen.
Es scheint im Allgemeinen nicht wahr zu sein, dass es einen Unterschied in der Menge des Holzeinschlags gibt (zumindest für insert / update / delete Operationen an der Tabelle selbst, obwohl ich seit gefunden dass es in dieser Hinsicht bei zwischengespeicherten temporären Objekten in gespeicherten Prozeduren aufgrund zusätzlicher Aktualisierungen der Systemtabelle einen kleinen Unterschied gibt).
Ich habe mir das Protokollierungsverhalten sowohl bei einem @table_variable und eine #temp Tabelle für die folgenden Vorgänge.
Die Aufzeichnungen im Transaktionsprotokoll waren für alle Vorgänge nahezu identisch.
Die Version mit den Tabellenvariablen hat tatsächlich ein paar extra Log-Einträge, da ein Eintrag in die Liste der Log-Einträge hinzugefügt (und später wieder entfernt) wird. sys.syssingleobjrefs Basistabelle, hatte aber insgesamt ein paar Bytes weniger, da der interne Name für Tabellenvariablen 236 Bytes weniger verbraucht als für #temp Tabellen (118 weniger) nvarchar Zeichen).
sqlcmd Modus):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),())Ergebnisse
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| | | | @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 |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
@wcm - die Tabellenvariable ist nicht nur für den Ram bestimmt - sie kann teilweise auf der Festplatte gespeichert werden.
Eine temporäre Tabelle kann Indizes haben, während eine Tabellenvariable nur einen Primärindex haben kann. Wenn Geschwindigkeit ein Thema ist, können Tabellenvariablen schneller sein, aber wenn es viele Datensätze gibt oder die Notwendigkeit besteht, die Temp-Tabelle eines Cluster-Indexes zu durchsuchen, dann wäre eine Temp-Tabelle besser.
Temp-Tabelle: Eine Temp-Tabelle ist einfach zu erstellen und Daten zu sichern.
Tabelle variabel: Aber die Tabellenvariable bringt den Aufwand mit sich, wenn wir normalerweise die normalen Tabellen erstellen.
Tabelle Temp: Das Ergebnis der Temp-Tabelle kann von mehreren Benutzern verwendet werden.
Tabelle variabel: Die Tabellenvariable kann jedoch nur von dem aktuellen Benutzer verwendet werden.
Tabelle Temp: Die Temp-Tabelle wird in der tempdb gespeichert. Sie verursacht Netzwerkverkehr. Wenn wir große Daten in der Temp-Tabelle haben, muss sie über die gesamte Datenbank arbeiten. Es wird ein Leistungsproblem geben.
Tabellenvariable: Eine Tabellenvariable speichert jedoch einen Teil der Daten im physischen Speicher und wird später, wenn die Größe zunimmt, in die Tempdb verschoben.
Tabelle Temp: Die Temp-Tabelle kann alle DDL-Operationen ausführen. Sie ermöglicht das Erstellen von Indizes, das Löschen, Ändern usw..,
Tabelle variabel: Die Tabellenvariable erlaubt keine DDL-Operationen. Aber die Tabellenvariable erlaubt es uns, nur den geclusterten Index zu erstellen.
Tabelle Temp: Die Temp-Tabelle kann für die aktuelle Sitzung oder global verwendet werden. So kann eine Sitzung mit mehreren Benutzern die Ergebnisse in der Tabelle nutzen.
Tabelle variabel: Aber die Tabellenvariable kann bis zu diesem Programm verwendet werden. (Gespeicherte Prozedur)
Tabelle Temp: Die Temp-Variable kann die Transaktionen nicht verwenden. Wenn wir die DML-Operationen mit der Temp-Tabelle tun, dann kann es Rollback oder Commit die Transaktionen sein.
Tabelle variabel: Bei Tabellenvariablen ist dies nicht möglich.
Tabelle Temp: Die Funktionen können die Temp-Variable nicht verwenden. Außerdem können wir die DML-Operation nicht in den Funktionen durchführen.
Tabelle variabel: Aber die Funktion erlaubt uns, die Tabellenvariable zu verwenden. Aber mit der Tabellenvariable können wir das tun.
Tabelle Temp: Die gespeicherte Prozedur wird neu kompiliert (kann nicht denselben Ausführungsplan verwenden), wenn wir die Temp-Variable für jeden Untersequenzaufruf verwenden.
Tabelle variabel: Die Tabellenvariable hingegen tut das nicht.
Zitat entnommen aus; Professionelle SQL Server 2012 Interna und Fehlerbehebung
Statistik Der Hauptunterschied zwischen temporären Tabellen und Tabellenvariablen ist, dass keine Statistiken über Tabellenvariablen erstellt werden. Dies hat zwei wesentliche Konsequenzen, von denen die erste darin besteht, dass der Query Optimizer eine eine feste Schätzung für die Anzahl der Zeilen in einer Tabellenvariablen unabhängig davon, welche Daten sie enthält. Außerdem ändert das Hinzufügen oder Entfernen Daten die Schätzung nicht ändern.
Indizes Sie können keine Indizes auf Tabellenvariablen erstellen, aber Sie können Beschränkungen erstellen. Das bedeutet, dass Sie durch das Erstellen von Primärschlüsseln oder eindeutigen Constraints Indizes auf Tabellenvariablen haben können (da diese zur Unterstützung von Constraints) auf Tabellenvariablen haben. Selbst wenn Sie Beschränkungen haben, und daher Indizes mit Statistiken haben, werden die Indizes nicht werden die Indizes nicht verwendet, wenn die Abfrage kompiliert wird, da sie zur Kompilier Kompilierungszeit nicht existieren und auch keine Neukompilierungen verursachen.
Schema-Änderungen Schema-Änderungen sind möglich bei temporären Tabellen möglich, nicht aber an Tabellenvariablen. Obwohl Schemamodifikationen auf temporären Tabellen möglich sind, sollten Sie diese vermeiden, da sie zu Neukompilierung von Anweisungen, die die Tabellen verwenden, verursachen.
TABELLENVARIABLEN WERDEN NICHT IM SPEICHER ANGELEGT
Es ist ein weit verbreiteter Irrglaube, dass Tabellenvariablen speicherinterne Strukturen sind. sind und als solche schneller arbeiten als temporäre Tabellen . Dank eines DMV namens sys . dm _ db _ session _ space _ usage , der die tempdb-Nutzung nach Sitzung, Sie können beweisen, dass das nicht der Fall ist. . Nach dem Neustart von SQL Server zum Löschen der DMV zu löschen, führen Sie das folgende Skript aus, um zu bestätigen, dass Ihre Session _ id 0 für user _ objects _ alloc _ page _ count :
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;Jetzt können Sie überprüfen, wie viel Platz eine temporäre Tabelle verbraucht, indem Sie folgendes ausführen Skript ausführen, um eine temporäre Tabelle mit einer Spalte zu erstellen und sie mit einer Zeile zu füllen:
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 ;Die Ergebnisse auf meinem Server zeigen, dass der Tabelle eine Seite in tempdb zugewiesen wurde. Führen Sie nun das gleiche Skript aus, aber verwenden Sie eine Tabellenvariable dieses Mal:
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 ;Welches ist zu verwenden?
Ob Sie temporäre Tabellen oder Tabellenvariablen verwenden oder nicht, sollte durch gründliche Tests entscheiden, aber ist es am besten, sich für eine vorübergehende Lösung zu entscheiden. Tabellen als Standard, weil es viel weniger Dinge gibt, die schief gehen können falsch .
Ich habe Kunden erlebt, die Code mit Tabellenvariablen entwickelt haben, weil sie mit einer kleinen Anzahl von Zeilen zu tun hatten, und das war schneller als eine temporäre Tabelle, aber ein paar Jahre später gab es Hunderte von tausende von Zeilen in der Tabellenvariable und die Leistung war schrecklich, Versuchen Sie also, bei Ihrer Entscheidung eine gewisse Kapazitätsplanung zu berücksichtigen Entscheidung!
CodeJaeger ist eine Gemeinschaft für Programmierer, die täglich Hilfe erhalten..
Wir haben viele Inhalte, und Sie können auch Ihre eigenen Fragen stellen oder die Fragen anderer Leute lösen.
