Synapse SQL의 임시 테이블
이 문서에서는 임시 테이블을 사용하기 위한 필수 지침을 제공하고 Synapse SQL 내의 세션 수준 임시 테이블의 원리를 강조해서 설명합니다.
전용 SQL 풀 및 서버리스 SQL 풀 리소스는 모두 임시 테이블을 사용할 수 있습니다. 서버리스 SQL 풀에는 이 문서의 끝 부분에서 설명하는 제한 사항이 있습니다.
임시 테이블
특히 중간 결과가 일시적인 변환 중 데이터를 처리할 때 임시 테이블은 유용합니다. Synapse SQL을 사용하면 임시 테이블이 세션 수준에 존재합니다. 이러한 임시 테이블은 만들어진 세션에만 표시됩니다. 따라서 해당 세션이 종료되면 자동으로 삭제됩니다.
전용 SQL 풀의 임시 테이블
전용 SQL 풀 리소스에서 임시 테이블은 결과가 원격 스토리지 대신 로컬로 기록되기 때문에 성능상의 이점을 제공합니다.
임시 테이블 만들기
임시 테이블은 테이블 이름 앞에 #을 붙여 만듭니다. 예시:
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
)
정확히 동일한 접근 방식을 사용하여 CTAS 를 통해 임시 테이블을 만들 수도 있습니다.
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]
)
;
참고 항목
CTAS는(은) 강력한 명령이며 트랜잭션 로그 공간을 사용한다는 점에서 효율적이라는 추가적인 이점이 있습니다.
임시 테이블 삭제
새 세션이 만들어지면 임시 테이블이 존재하지 않습니다. 그러나 동일한 이름으로 임시 테이블을 생성하는 동일한 저장 프로시저를 호출하는 경우 CREATE TABLE 문이 성공적인지 확인하려면 DROP과 함께 간단한 사전 존재 여부 확인을 사용합니다.
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
코딩 일관성을 유지하려면 테이블 및 임시 테이블 모두에 대해 이 패턴을 사용하는 것이 좋습니다. 또한 사용을 완료한 경우 DROP TABLE을 사용하여 임시 테이블을 제거하는 것이 좋습니다.
저장 프로시저 개발에서는 프로시저 끝에 삭제 명령이 번들로 포함되어 있는지 확인하여 이러한 개체가 정리되는지 알 수 있습니다.
DROP TABLE #stats_ddl
코드 모듈화
임시 테이블은 사용자 세션의 모든 위치에서 사용할 수 있습니다. 그러면 이 기능을 활용하여 애플리케이션 코드를 모듈화할 수 있습니다. 설명하기 위해 다음 저장 프로시저는 다음 통계 이름으로 데이터베이스의 모든 통계를 업데이트하는 DDL을 생성합니다.
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
이 단계에서 발생하는 유일한 작업은 #stats_ddl 임시 테이블을 생성하는 저장 프로시저를 만드는 것입니다. 저장 프로시저는 이미 있는 경우 #stats_ddl을 삭제합니다. 이를 삭제하면 세션 내에서 두 번 이상 실행되는 경우 실패하지 않습니다.
저장 프로시저 끝에는 DROP TABLE이 없으므로 저장 프로시저가 완료되면 저장 프로시저 외부에서 읽을 수 있도록 만든 테이블이 그대로 남아 있습니다.
다른 SQL Server 데이터베이스와 달리 Synapse SQL에서는 임시 테이블을 만든 프로시저 외부에서 임시 테이블을 사용할 수 있습니다. 전용 SQL 풀을 통해 생성된 임시 테이블은 세션 내 어디서나 사용할 수 있습니다. 결과적으로 아래 샘플에서 볼 수 있듯이 더 모듈화되고 관리하기 쉬운 코드를 사용할 수 있습니다.
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;
임시 테이블 제한 사항
전용 SQL 풀에는 임시 테이블에 대한 몇 가지 구현 제한 사항이 있습니다.
- 세션 범위의 임시 테이블만 지원됩니다. 전역 임시 테이블은 지원되지 않습니다.
- 임시 테이블에서 뷰를 만들 수 없습니다.
- 임시 테이블은 해시 또는 라운드 로빈 배포를 통해서만 만들 수 있습니다. 복제된 임시 테이블 배포가 지원되지 않습니다.
서버리스 SQL 풀의 임시 테이블
서버리스 SQL 풀의 임시 테이블은 지원되지만 사용이 제한됩니다. 이러한 파일은 파일을 대상으로 하는 쿼리에서 사용할 수 없습니다.
예를 들어 스토리지에 있는 파일의 데이터를 사용하여 임시 테이블에 조인할 수 없습니다. 임시 테이블의 수는 100개로 제한되고 총 크기는 100MB로 제한됩니다.
다음 단계
테이블 개발에 대한 자세한 내용은 Synapse SQL 리소스를 사용한 테이블 디자인 문서를 참조하세요.