Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Dieser Artikel enthält wichtige Anleitungen für die Verwendung temporärer Tabellen und hebt die Prinzipien temporärer Tabellen auf Sitzungsebene hervor.
Die Verwendung der Informationen in diesem Artikel kann Ihnen helfen, Ihren Code zu modularisieren und sowohl die Wiederverwensbarkeit als auch die Einfache Wartung zu verbessern.
Was sind temporäre Tabellen?
Temporäre Tabellen sind beim Verarbeiten von Daten nützlich, insbesondere während der Transformation, bei der die Zwischenergebnisse vorübergehend sind. In dediziertem SQL-Pool sind temporäre Tabellen auf Sitzungsebene vorhanden.
Temporäre Tabellen sind nur für die Sitzung sichtbar, in der sie erstellt wurden, und werden automatisch gelöscht, wenn diese Sitzung geschlossen wird.
Temporäre Tabellen bieten einen Leistungsvorteil, da ihre Ergebnisse nicht in Remotespeicher, sondern in den lokalen Speicher geschrieben werden.
Temporäre Tabellen im dedizierten SQL-Pool
In der dedizierten SQL-Poolressource bieten temporäre Tabellen einen Leistungsvorteil, da ihre Ergebnisse nicht in Remotespeicher, sondern lokal geschrieben werden.
Erstellen einer temporären Tabelle
Temporäre Tabellen werden erstellt, indem Dem Tabellennamen ein #Präfix vorangestellt wird. Beispiel:
CREATE TABLE #stats_ddl
(
[schema_name] NVARCHAR(128) NOT NULL
, [table_name] NVARCHAR(128) NOT NULL
, [stats_name] NVARCHAR(128) NOT NULL
, [stats_is_filtered] BIT NOT NULL
, [seq_nmbr] BIGINT NOT NULL
, [two_part_name] NVARCHAR(260) NOT NULL
, [three_part_name] NVARCHAR(400) NOT NULL
)
WITH
(
DISTRIBUTION = HASH([seq_nmbr])
, HEAP
)
Mit einem CTAS können temporäre Tabellen auch nach genau demselben Ansatz erstellt werden.
CREATE TABLE #stats_ddl
WITH
(
DISTRIBUTION = HASH([seq_nmbr])
, HEAP
)
AS
(
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[has_filter] AS [stats_is_filtered]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [two_part_name]
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [three_part_name]
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
GROUP BY
sm.[name]
, tb.[name]
, st.[name]
, st.[filter_definition]
, st.[has_filter]
)
;
Hinweis
CTAS ist ein leistungsstarker Befehl und hat den zusätzlichen Vorteil, effizient bei der Verwendung des Transaktionsprotokollraums zu sein.
Temporäre Tabellen ablegen
Wenn eine neue Sitzung erstellt wird, sollten keine temporären Tabellen vorhanden sein.
Wenn Sie dieselbe gespeicherte Prozedur aufrufen, die eine temporäre Prozedur mit demselben Namen erstellt, um sicherzustellen, dass Ihre CREATE TABLE Anweisungen erfolgreich sind, kann eine einfache Vorabüberprüfung mit einer DROP wie im folgenden Beispiel verwendet werden:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Um Konsistenz zu codieren, empfiehlt es sich, dieses Muster sowohl für Tabellen als auch für temporäre Tabellen zu verwenden. Außerdem ist es ratsam, DROP TABLE zu verwenden, um temporäre Tabellen zu entfernen, wenn Sie sie in Ihrem Code nicht mehr benötigen.
Bei der Entwicklung gespeicherter Prozeduren ist es üblich, dass die Dropbefehle am Ende einer Prozedur gebündelt sind, um sicherzustellen, dass diese Objekte bereinigt werden.
DROP TABLE #stats_ddl
Modularisieren von Code
Da temporäre Tabellen an einer beliebigen Stelle in einer Benutzersitzung angezeigt werden können, kann diese Funktion genutzt werden, damit Sie Ihren Anwendungscode modularisieren können.
Die folgende gespeicherte Prozedur generiert z. B. DDL, um alle Statistiken in der Datenbank anhand des Statistiknamens zu aktualisieren:
CREATE PROCEDURE [dbo].[prc_sqldw_update_stats]
( @update_type tinyint -- 1 default 2 fullscan 3 sample 4 resample
,@sample_pct tinyint
)
AS
IF @update_type NOT IN (1,2,3,4)
BEGIN;
THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;
IF @sample_pct IS NULL
BEGIN;
SET @sample_pct = 20;
END;
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
CREATE TABLE #stats_ddl
WITH
(
DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[has_filter] AS [stats_is_filtered]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [two_part_name]
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [three_part_name]
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
GROUP BY
sm.[name]
, tb.[name]
, st.[name]
, st.[filter_definition]
, st.[has_filter]
)
SELECT
CASE @update_type
WHEN 1
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
WHEN 2
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
WHEN 3
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
WHEN 4
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
END AS [update_stats_ddl]
, [seq_nmbr]
FROM #stats_ddl
;
GO
In dieser Phase ist die einzige Aktion, die aufgetreten ist, die Erstellung einer gespeicherten Prozedur, die eine temporäre Tabelle generiert, #stats_ddlmit DDL-Anweisungen.
Diese gespeicherte Prozedur legt eine vorhandene #stats_ddl Prozedur ab, um sicherzustellen, dass sie nicht fehlschlägt, wenn sie mehr als einmal innerhalb einer Sitzung ausgeführt wird.
Da es am Ende der gespeicherten Prozedur kein DROP TABLE gibt, bleibt die erstellte Tabelle bestehen, sodass sie außerhalb der gespeicherten Prozedur gelesen werden kann.
Im dedizierten SQL-Pool ist es im Gegensatz zu anderen SQL Server-Datenbanken möglich, die temporäre Tabelle außerhalb der Prozedur zu verwenden, die sie erstellt hat. Dedizierte temporäre SQL-Pooltabellen können überall in der Sitzung verwendet werden. Dieses Feature kann zu einem modulareren und verwaltbaren Code führen, wie im folgenden Beispiel gezeigt:
EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;
DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
WHILE @i <= @t
BEGIN
SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);
PRINT @s
EXEC sp_executesql @s
SET @i+=1;
END
DROP TABLE #stats_ddl;
Einschränkungen für temporäre Tabellen
Der dedizierte SQL-Pool legt beim Implementieren temporärer Tabellen einige Einschränkungen fest. Derzeit werden nur temporäre Tabellen mit Sitzungsbereich unterstützt. Globale temporäre Tabellen werden nicht unterstützt.
Außerdem können Ansichten nicht in temporären Tabellen erstellt werden. Temporäre Tabellen können nur mit Hash- oder Roundrobinverteilung erstellt werden. Replizierte temporäre Tabellenverteilung wird nicht unterstützt.
Nächste Schritte
Weitere Informationen zum Entwickeln von Tabellen finden Sie im Artikel " Entwerfen von Tabellen mit dediziertem SQL-Pool ".