Dočasné tabulky ve vyhrazeném fondu SQL ve službě Azure Synapse Analytics
Tento článek obsahuje základní pokyny k používání dočasných tabulek a zdůrazňuje principy dočasných tabulek na úrovni relace.
Použití informací v tomto článku vám může pomoct modularizovat kód a zlepšit tak opětovnou použitelnost a snadnou údržbu.
Co jsou dočasné tabulky?
Dočasné tabulky jsou užitečné při zpracování dat, zejména při transformaci, kdy jsou přechodné výsledky přechodné. Ve vyhrazeném fondu SQL existují dočasné tabulky na úrovni relace.
Dočasné tabulky jsou viditelné pouze pro relaci, ve které byly vytvořeny, a při ukončení relace se automaticky zahodí.
Dočasné tabulky nabízejí výhodu výkonu, protože jejich výsledky se zapisují do místního úložiště, nikoli do vzdáleného úložiště.
Dočasné tabulky ve vyhrazeném fondu SQL
Ve vyhrazeném prostředku fondu SQL nabízejí dočasné tabulky výhodu výkonu, protože jejich výsledky se zapisují do místního úložiště, nikoli do vzdáleného úložiště.
Vytvoření dočasné tabulky
Dočasné tabulky se vytvářejí tak, že k názvu tabulky přidáte předponu #. Příklad:
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
)
K vytvoření CTAS dočasných tabulek můžete použít stejný přístup:
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]
)
;
Poznámka
CTAS je výkonný příkaz a má další výhodu v efektivním využití prostoru pro transakční protokoly.
Vyřazení dočasných tabulek
Při vytvoření nové relace by neměly existovat žádné dočasné tabulky.
Pokud voláte stejnou uloženou proceduru, která vytvoří dočasnou proceduru se stejným názvem, aby se zajistilo, že příkazy CREATE TABLE budou úspěšné, můžete použít jednoduchou kontrolu před existencí s parametrem DROP jako v následujícím příkladu:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Z důvodu konzistence kódování je vhodné tento vzor použít pro tabulky i dočasné tabulky. Je také vhodné použít DROP TABLE k odebrání dočasných tabulek, když jste s nimi v kódu skončili.
Při vývoji uložených procedur je běžné vidět příkazy pro vyřazení, které jsou na konci procedury seskupené dohromady, aby se zajistilo, že se tyto objekty vyčistí.
DROP TABLE #stats_ddl
Modularizace kódu
Vzhledem k tomu, že dočasné tabulky lze zobrazit kdekoli v uživatelské relaci, můžete tuto funkci využít k tomu, abyste mohli modularizovat kód aplikace.
Například následující uložená procedura vygeneruje DDL pro aktualizaci všech statistik v databázi podle názvu statistiky:
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
V této fázi je jedinou akcí, ke které došlo, vytvoření uložené procedury, #stats_ddlkterá generuje dočasnou tabulku s příkazy DDL.
Tato uložená procedura zahodí existující #stats_ddl proceduru, aby se zajistilo, že nedojde k jejímu selhání, pokud se v rámci relace spustí více než jednou.
Vzhledem k tomu, že na konci uložené procedury není žádná DROP TABLE , po dokončení uložené procedury opustí vytvořenou tabulku, aby ji bylo možné číst mimo uloženou proceduru.
Ve vyhrazeném fondu SQL je na rozdíl od jiných SQL Server databází možné použít dočasnou tabulku mimo proceduru, která ji vytvořila. Dočasné tabulky vyhrazeného fondu SQL je možné použít kdekoli v rámci relace. Tato funkce může vést k modulárnímu a spravovatelnému kódu jako v následujícím příkladu:
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;
Dočasná omezení tabulek
Vyhrazený fond SQL má při implementaci dočasných tabulek několik omezení. V současné době jsou podporovány pouze dočasné tabulky s oborem relace. Globální dočasné tabulky se nepodporují.
U dočasných tabulek se také nedají vytvářet zobrazení. Dočasné tabulky je možné vytvořit pouze pomocí distribuce hodnot hash nebo kruhového dotazování. Replikovaná distribuce dočasných tabulek se nepodporuje.
Další kroky
Další informace o vývoji tabulek najdete v článku Návrh tabulek pomocí vyhrazeného fondu SQL .