Tijdelijke tabellen in toegewezen SQL-pool in Azure Synapse Analytics
Dit artikel bevat essentiële richtlijnen voor het gebruik van tijdelijke tabellen en belicht de principes van tijdelijke tabellen op sessieniveau.
Met behulp van de informatie in dit artikel kunt u uw code modulariseren, waardoor zowel herbruikbaar als onderhoudsgemak worden verbeterd.
Wat zijn tijdelijke tabellen?
Tijdelijke tabellen zijn handig bij het verwerken van gegevens, met name tijdens transformaties waarbij de tussenliggende resultaten tijdelijk zijn. In een toegewezen SQL-pool bestaan tijdelijke tabellen op sessieniveau.
Tijdelijke tabellen zijn alleen zichtbaar voor de sessie waarin ze zijn gemaakt en worden automatisch verwijderd wanneer die sessie wordt gesloten.
Tijdelijke tabellen bieden een prestatievoordeel omdat de resultaten naar lokale in plaats van naar externe opslag worden geschreven.
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 dezelfde opgeslagen procedure aanroept, waarmee een tijdelijke procedure met dezelfde naam wordt gemaakt, CREATE TABLE kunt u een eenvoudige pre-existentiecontrole met een DROP gebruiken, zoals in het volgende voorbeeld:
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 klaar bent met deze tabellen in uw code.
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
Omdat tijdelijke tabellen overal in een gebruikerssessie kunnen worden weergegeven, kan deze mogelijkheid worden gebruikt om u te helpen bij het modulariseren van uw toepassingscode.
Met de volgende opgeslagen procedure wordt bijvoorbeeld DDL gegenereerd 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 een tijdelijke tabel, , met DDL-instructies, #stats_ddlwordt gegenereerd.
Met deze opgeslagen procedure wordt een bestaande #stats_ddl verwijderd om ervoor te zorgen dat deze niet mislukt als deze meer dan één keer binnen een sessie wordt uitgevoerd.
Omdat er echter geen DROP TABLE is aan het einde van de opgeslagen procedure, blijft de opgeslagen procedure na voltooiing van de opgeslagen procedure de gemaakte tabel staan, zodat deze buiten de opgeslagen procedure kan worden gelezen.
In een toegewezen SQL-pool is het, in tegenstelling tot andere SQL Server databases, mogelijk om de tijdelijke tabel te gebruiken buiten de procedure waarmee deze is gemaakt. Tijdelijke tabellen van toegewezen SQL-pools kunnen overal in de sessie worden gebruikt. Deze functie kan leiden tot meer modulaire en beheerbare code, zoals in het volgende voorbeeld:
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 legt een aantal beperkingen op bij het implementeren van tijdelijke tabellen. Momenteel worden alleen tijdelijke tabellen met sessiebereik ondersteund. Globale tijdelijke tabellen worden niet ondersteund.
Er kunnen ook 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.
Volgende stappen
Zie het artikel Tabellen ontwerpen met een toegewezen SQL-pool voor meer informatie over het ontwikkelen van tabellen.