Ideiglenes táblák a Synapse SQL-ben
Ez a cikk alapvető útmutatást tartalmaz az ideiglenes táblák használatához, és kiemeli a Synapse SQL munkamenetszintű ideiglenes tábláinak alapelveit.
A dedikált SQL-készlet és a kiszolgáló nélküli SQL-készlet erőforrásai is használhatnak ideiglenes táblákat. A kiszolgáló nélküli SQL-készletre a cikk végén ismertetett korlátozások vonatkoznak.
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 Synapse SQL-ben ideiglenes táblák léteznek a munkamenet szintjén. Csak az a munkamenet látható, amelyben létrehozták őket. A rendszer így automatikusan elveti őket, amikor a munkamenet véget ér.
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 azonban ugyanazt a tárolt eljárást hívja meg, amely egy azonos nevű ideiglenes eljárást hoz létre, az utasítások sikerességének biztosításához CREATE TABLE használjon egy egyszerű, létezés előtti ellenőrzést a következővel 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. Érdemes az ideiglenes táblákat is DROP TABLE eltávolítani, ha elkészült velük.
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
Az ideiglenes táblák a felhasználói munkamenetekben bárhol használhatók. Ezt a képességet kihasználva modulárissá teheti az alkalmazás kódját. Ennek bizonyításához az alábbi tárolt eljárás létrehozza a 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, ami történt, egy tárolt eljárás létrehozása, amely létrehozza a #stats_ddl ideiglenes táblát. A tárolt eljárás #stats_ddl csökken, ha már létezik. Ez az elvetés biztosítja, hogy ne hiúsuljon meg, ha egy munkameneten belül többször fut.
Mivel a tárolt eljárásnak nincs DROP TABLE vége, a tárolt eljárás befejeződésekor a létrehozott tábla megmarad, és a tárolt eljáráson kívül is olvasható.
A többi SQL Server adatbázissal ellentétben a Synapse SQL lehetővé teszi az ideiglenes tábla használatát az azt létrehozó eljáráson kívül. A dedikált SQL-készleten keresztül létrehozott ideiglenes táblák a munkameneten belül bárhol használhatók. Ennek eredményeképpen modulárisabb és kezelhetőbb kóddal fog rendelkezni, ahogy az alábbi példában is látható:
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 megvalósítási korlátozással rendelkezik az ideiglenes táblákra vonatkozóan:
- Csak a munkamenet-hatókörű ideiglenes táblák támogatottak. A globális ideiglenes táblák nem támogatottak.
- Ideiglenes táblákon nem hozhatók létre nézetek.
- 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.
Ideiglenes táblák kiszolgáló nélküli SQL-készletben
A kiszolgáló nélküli SQL-készletekben támogatottak az ideiglenes táblák, de a használatuk korlátozott. Fájlokat célzó lekérdezésekben nem használhatók.
Például nem kapcsolhat össze ideiglenes táblát a tárolóban lévő fájlokból származó adatokkal. Az ideiglenes táblák száma 100-ra van korlátozva, és a teljes méretük legfeljebb 100 MB lehet.
Következő lépések
A táblák fejlesztéséről további információt a Táblák tervezése a Synapse SQL-erőforrások használatával című cikkben talál.