Ideiglenes táblák dedikált SQL-készletben a Azure Synapse Analyticsben
Ez a cikk alapvető útmutatást tartalmaz az ideiglenes táblák használatához, és kiemeli a munkamenetszintű ideiglenes táblák alapelveit.
A cikkben található információk segítségével modulárissá teheti a kódot, és javíthatja az újrafelhasználhatóságot és a könnyű karbantartást.
Mik azok az ideiglenes táblák?
Az ideiglenes táblák hasznosak az adatok feldolgozásakor, különösen az átalakítás során, ahol a köztes eredmények átmenetiek. A dedikált SQL-készletben ideiglenes táblák találhatók a munkamenet szintjén.
Az ideiglenes táblák csak abban a munkamenetben láthatók, amelyben létrehozták őket, és a munkamenet bezárásakor automatikusan törlődnek.
Az ideiglenes táblák teljesítménybeli előnyökkel járnak, mert az eredményeik a távoli tárolás helyett helyire vannak írva.
Ideiglenes táblák dedikált SQL-készletben
A dedikált SQL-készlet erőforrásában az ideiglenes táblák teljesítményelőnnyel járnak, mivel eredményeik a távoli tárolás helyett helyire vannak írva.
Ideiglenes tábla létrehozása
Az ideiglenes táblák a tábla nevének előtaggal történő előtagolásával jönnek létre.# Például:
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
)
Az ideiglenes táblák pontosan ugyanazzal CTAS a módszerrel is létrehozhatók:
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]
)
;
Megjegyzés
CTAS egy hatékony parancs, és azzal az előnnyel jár, hogy hatékonyan használja a tranzakciónapló-területet.
Ideiglenes táblák elvetése
Új munkamenet létrehozásakor nem létezhetnek ideiglenes táblák.
Ha ugyanazt a tárolt eljárást hívja meg, amely egy azonos nevű ideiglenes eljárást hoz létre, hogy CREATE TABLE az utasítások sikeresek legyenek, az alábbi példához hasonlóan használhat egy egyszerű elő-létezési ellenőrzést az a-val DROP :
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
A konzisztencia kódolásához ajánlott ezt a mintát mind a táblákhoz, mind az ideiglenes táblákhoz használni. Az ideiglenes táblák eltávolítására is érdemes használni DROP TABLE , ha befejezte azokat a kódban.
A tárolt eljárások fejlesztése során gyakran előfordul, hogy az eljárás végén a drop parancsok össze vannak csomagolva, hogy ezek az objektumok törlődjenek.
DROP TABLE #stats_ddl
Kód modularizálása
Mivel az ideiglenes táblák bárhol láthatók egy felhasználói munkamenetben, ezt a képességet kihasználva modulárissá teheti az alkalmazás kódját.
Az alábbi tárolt eljárás például létrehoz egy DDL-t az adatbázis összes statisztikájának statisztikai név szerinti frissítéséhez:
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
Ebben a szakaszban az egyetlen művelet, amely egy tárolt eljárás létrehozása, amely létrehoz egy ideiglenes táblát, #stats_ddlDDL-utasításokkal.
Ez a tárolt eljárás elvet egy meglévőt #stats_ddl annak biztosítása érdekében, hogy ne hiúsuljon meg, ha egy munkameneten belül többször fut.
Mivel DROP TABLE azonban nincs a tárolt eljárás végén, a tárolt eljárás befejeződésekor elhagyja a létrehozott táblát, hogy a tárolt eljáráson kívül is olvasható legyen.
A dedikált SQL-készletben a többi SQL Server adatbázistól eltérően az ideiglenes táblát az azt létrehozó eljáráson kívül is használhatja. A dedikált SQL-készlet ideiglenes táblái a munkameneten belül bárhol használhatók. Ez a funkció modulárisabb és kezelhetőbb kódhoz vezethet, mint az alábbi példában:
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;
Ideiglenes táblakorlátozások
A dedikált SQL-készlet néhány korlátozást ír elő az ideiglenes táblák implementálásakor. Jelenleg csak a munkamenet-hatókörű ideiglenes táblák támogatottak. A globális ideiglenes táblák nem támogatottak.
A nézetek ideiglenes táblákon sem hozhatók létre. Ideiglenes táblák csak kivonatos vagy ciklikus időszeleteléses eloszlással hozhatók létre. A replikált ideiglenes táblaterjesztés nem támogatott.
Következő lépések
A táblák fejlesztéséről további információt a Táblák tervezése dedikált SQL-készlet használatával című cikkben talál.