Tijdelijke tabellen in toegewezen SQL-pool in Azure Synapse Analytics
Dit artikel bevat essentiële richtlijnen voor het gebruik van tijdelijke tabellen en markeert de principes van tijdelijke tabellen op sessieniveau.
Met behulp van de informatie in dit artikel kunt u uw code modulariseren, waardoor de herbruikbaarheid en het onderhoudsgemak worden verbeterd.
Wat zijn tijdelijke tabellen?
Tijdelijke tabellen zijn handig bij het verwerken van gegevens, met name tijdens de transformatie 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 hun resultaten worden geschreven naar lokale in plaats van externe opslag.
Tijdelijke tabellen in toegewezen SQL-pool
In de toegewezen SQL-poolresource bieden tijdelijke tabellen een prestatievoordeel omdat hun resultaten worden weggeschreven naar lokale in plaats van externe opslag.
Een tijdelijke tabel maken
Tijdelijke tabellen worden gemaakt door de tabelnaam vooraf te laten gaan door een #. Voorbeeld:
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 dezelfde CTAS methode:
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 is in het gebruik van transactielogboekruimte.
Tijdelijke tabellen verwijderen
Wanneer er een nieuwe sessie wordt gemaakt, mogen er geen tijdelijke tabellen bestaan.
Als u dezelfde opgeslagen procedure aanroept, die een tijdelijke structuur met dezelfde naam creëert, om ervoor te zorgen dat uw CREATE TABLE-instructies succesvol zijn, kunt u een eenvoudige pre-existentiecontrole uitvoeren met een DROP-controle zoals in het volgende voorbeeld:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Voor het coderen van consistentie is het een goed idee om dit patroon te gebruiken voor zowel tabellen als tijdelijke tabellen. Het is ook een goed idee om tijdelijke tabellen te DROP TABLE verwijderen wanneer u klaar bent met deze tabellen in uw code.
Bij het ontwikkelen van opgeslagen procedures is het gebruikelijk dat de verwijderingsopdrachten aan het einde van een procedure gebundeld worden, zodat deze objecten worden verwijderd.
DROP TABLE #stats_ddl
Code modulariseren
Aangezien tijdelijke tabellen overal in een gebruikerssessie kunnen worden gezien, kan deze mogelijkheid worden gebruikt om u te helpen uw toepassingscode te modulariseren.
Met de volgende opgeslagen procedure wordt bijvoorbeeld DDL gegenereerd om alle statistieken in de database bij te werken op statistieknaam:
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 wordt gegenereerd, #stats_ddlmet DDL-instructies.
Met deze opgeslagen procedure wordt een bestaande #stats_ddl procedure verwijderd om ervoor te zorgen dat deze niet mislukt als deze meerdere keren binnen een sessie wordt uitgevoerd.
Aangezien er echter geen DROP TABLE aan het einde van de opgeslagen procedure is, blijft de aangemaakte tabel bestaan, zodat deze buiten de opgeslagen procedure kan worden gelezen.
In een toegewezen SQL-pool, in tegenstelling tot andere SQL Server-databases, is het mogelijk om de tijdelijke tabel te gebruiken buiten de procedure die deze heeft gemaakt. Tijdelijke tabellen voor 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 een hash- of round-robinverdeling. Gerepliceerde tijdelijke tabeldistributie wordt niet ondersteund.
Volgende stappen
Zie het artikel Tabellen ontwerpen met behulp van een toegewezen SQL-pool voor meer informatie over het ontwikkelen van tabellen.