Tijdelijke tabellen in Synapse SQL
Dit artikel bevat essentiële richtlijnen voor het gebruik van tijdelijke tabellen en belicht de principes van tijdelijke tabellen op sessieniveau in Synapse SQL.
Zowel de toegewezen SQL-pool als de serverloze SQL-poolresources kunnen gebruikmaken van tijdelijke tabellen. Serverloze SQL-pool heeft beperkingen die aan het einde van dit artikel worden besproken.
Tijdelijke tabellen
Tijdelijke tabellen zijn handig bij het verwerken van gegevens, met name tijdens transformaties waarbij de tussenliggende resultaten tijdelijk zijn. Met Synapse SQL bestaan tijdelijke tabellen op sessieniveau. Ze zijn alleen zichtbaar voor de sessie waarin ze zijn gemaakt. Als zodanig worden ze automatisch verwijderd wanneer die sessie wordt beëindigd.
Tijdelijke tabellen in toegewezen SQL-pool
In de toegewezen SQL-poolresource bieden tijdelijke tabellen een prestatievoordeel, omdat de resultaten worden geschreven naar lokale in plaats van externe opslag.
Een tijdelijke tabel maken
Tijdelijke tabellen worden gemaakt door het voorvoegsel van uw tabelnaam te voorzien van een #. Bijvoorbeeld:
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
)
Tijdelijke tabellen kunnen ook worden gemaakt met een CTAS met exact dezelfde aanpak:
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]
)
;
Notitie
CTAS is een krachtige opdracht en heeft het extra voordeel dat het efficiënt gebruik van transactielogboekruimte is.
Tijdelijke tabellen verwijderen
Wanneer een nieuwe sessie wordt gemaakt, mogen er geen tijdelijke tabellen bestaan. Als u echter dezelfde opgeslagen procedure aanroept waarmee een tijdelijke procedure met dezelfde naam wordt gemaakt, gebruikt CREATE TABLE u een eenvoudige controle voor het bestaan van uw instructies met DROP:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Voor codeconsistentie is het een goed idee om dit patroon te gebruiken voor zowel tabellen als tijdelijke tabellen. Het is ook een goed idee om DROP TABLE tijdelijke tabellen te verwijderen wanneer u er klaar mee bent.
Bij het ontwikkelen van opgeslagen procedures is het gebruikelijk dat de opdrachten voor neerzetten aan het einde van een procedure worden gebundeld om ervoor te zorgen dat deze objecten worden opgeschoond.
DROP TABLE #stats_ddl
Code modulariseren
Tijdelijke tabellen kunnen overal in een gebruikerssessie worden gebruikt. Deze mogelijkheid kan vervolgens worden gebruikt om u te helpen uw toepassingscode modulariseren. Ter illustratie genereert de volgende opgeslagen procedure DDL om alle statistieken in de database bij te werken op statistische naam:
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 deze fase is de enige actie die is opgetreden het maken van een opgeslagen procedure waarmee de #stats_ddl tijdelijke tabel wordt gegenereerd. De opgeslagen procedure wordt #stats_ddl als deze al bestaat. Deze daling zorgt ervoor dat deze niet mislukt als het meer dan één keer binnen een sessie wordt uitgevoerd.
Omdat er geen DROP TABLE aan het einde van de opgeslagen procedure is, blijft de gemaakte tabel na voltooiing van de opgeslagen procedure behouden en kan deze buiten de opgeslagen procedure worden gelezen.
In tegenstelling tot andere SQL Server databases kunt u met Synapse SQL de tijdelijke tabel gebruiken buiten de procedure waarmee deze is gemaakt. De tijdelijke tabellen die zijn gemaakt via een toegewezen SQL-pool, kunnen overal in de sessie worden gebruikt. Als gevolg hiervan hebt u meer modulaire en beheerbare code, zoals in het onderstaande voorbeeld wordt gedemonstreerd:
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;
Tijdelijke tabelbeperkingen
Toegewezen SQL-pool heeft enkele implementatiebeperkingen voor tijdelijke tabellen:
- Alleen tijdelijke tabellen met sessiebereik worden ondersteund. Globale tijdelijke tabellen worden niet ondersteund.
- Er kunnen geen weergaven worden gemaakt voor tijdelijke tabellen.
- Tijdelijke tabellen kunnen alleen worden gemaakt met hash- of round robin-distributie. Gerepliceerde tijdelijke tabeldistributie wordt niet ondersteund.
Tijdelijke tabellen in serverloze SQL-pool
Tijdelijke tabellen in een serverloze SQL-pool worden ondersteund, maar het gebruik ervan is beperkt. Ze kunnen niet worden gebruikt in query's die zijn gericht op bestanden.
U kunt bijvoorbeeld geen tijdelijke tabel koppelen met gegevens uit bestanden in opslag. Het aantal tijdelijke tabellen is beperkt tot 100 en de totale grootte is beperkt tot 100 MB.
Volgende stappen
Zie het artikel Tabellen ontwerpen met behulp van synapse SQL-resources voor meer informatie over het ontwikkelen van tabellen.