Tabelle temporanee in Synapse SQL
Questo articolo contiene indicazioni essenziali per l'uso di tabelle temporanee ed evidenzia i principi delle tabelle temporanee a livello di sessione all'interno di Synapse SQL.
Sia il pool SQL dedicato che le risorse del pool SQL serverless possono usare tabelle temporanee. Il pool SQL serverless presenta limitazioni descritte alla fine di questo articolo.
Tabelle temporanee
Le tabelle temporanee sono utili per l'elaborazione dati, soprattutto durante la trasformazione in cui i risultati intermedi sono temporanei. Con Synapse SQL, le tabelle temporanee esistono a livello di sessione. Sono visibili solo per la sessione in cui sono state create. Di conseguenza, vengono eliminati automaticamente al termine della sessione.
Tabelle temporanee nel pool SQL dedicato
Nella risorsa del pool SQL dedicato le tabelle temporanee offrono un vantaggio per le prestazioni perché i risultati vengono scritti in locale anziché nell'archiviazione remota.
Creazione di una tabella temporanea
Le tabelle temporanee vengono create aggiungendo un prefisso al nome di una tabella con #
. Ad esempio:
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
)
Le tabelle temporanee possono essere create anche con CTAS
adottando esattamente lo stesso approccio:
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]
)
;
Nota
CTAS
è un comando efficace e offre l'ulteriore vantaggio di essere efficiente nell'uso dello spazio dei log delle transazioni.
Eliminare le tabelle temporanee
Quando viene creata una nuova sessione, non deve esistere alcuna tabella temporanea. Tuttavia, se si chiama la stessa stored procedure che crea un temporaneo con lo stesso nome, per assicurarsi che CREATE TABLE
le istruzioni siano riuscite, usare un semplice controllo preesistente con DROP
:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Una procedura consigliata per la coerenza del codice consiste nell'usare questo modello sia per le tabelle che per le tabelle temporanee. È anche consigliabile usare DROP TABLE
per rimuovere le tabelle temporanee al termine dell'operazione.
Nello sviluppo di stored procedure in genere i comandi di eliminazione vengono raggruppati alla fine di una procedura per assicurare che questi oggetti vengano puliti.
DROP TABLE #stats_ddl
Modularizzare il codice
Le tabelle temporanee possono essere usate ovunque in una sessione utente. Questa funzionalità può quindi essere sfruttata per facilitare la modularizzazione del codice dell'applicazione. Per dimostrare, la stored procedure seguente genera DDL per aggiornare tutte le statistiche nel database in base al nome della statistica:
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 questa fase, l'unica azione che si è verificata è la creazione di una stored procedure che genera la tabella temporanea #stats_ddl. La stored procedure elimina #stats_ddl se esiste già. Questa eliminazione garantisce che non si verifichi un errore se l'esecuzione viene eseguita più volte all'interno di una sessione.
Poiché non è presente un DROP TABLE
oggetto alla fine della stored procedure, al termine della stored procedure, la tabella creata rimane e può essere letta all'esterno della stored procedure.
A differenza di altri database SQL Server, Synapse SQL consente di usare la tabella temporanea all'esterno della procedura che l'ha creata. Le tabelle temporanee create tramite il pool SQL dedicato possono essere usate ovunque all'interno della sessione. Di conseguenza, si avrà un codice più modulare e gestibile, come illustrato nell'esempio seguente:
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;
Limitazioni della tabella temporanea
Il pool SQL dedicato presenta alcune limitazioni di implementazione per le tabelle temporanee:
- Sono supportate solo le tabelle temporanee con ambito sessione. Le tabelle temporanee globali non sono supportate.
- Non è possibile creare viste nelle tabelle temporanee.
- Le tabelle temporanee possono essere create solo con la distribuzione hash o round robin. La distribuzione di tabelle temporanee replicate non è supportata.
Tabelle temporanee nel pool SQL serverless
Le tabelle temporanee nel pool SQL serverless sono supportate, ma l'utilizzo è limitato. Non possono essere usate nelle query che hanno come destinazione i file.
Ad esempio, non è possibile aggiungere una tabella temporanea con i dati dei file nella risorsa di archiviazione. Il numero di tabelle temporanee è limitato a 100 e le dimensioni totali sono limitate a 100 MB.
Passaggi successivi
Per altre informazioni sullo sviluppo di tabelle, vedere l'articolo Progettazione di tabelle con le risorse Synapse SQL.