SQL Server 2005에서는 다음 두 가지 방법 중 하나를 사용하여 임시 테이블을 만들 수 있습니다.
declare @tmp table (Col1 int, Col2 int);또는
create table #tmp (Col1 int, Col2 int);이 둘의 차이점은 무엇입니까? @tmp가 여전히 tempdb를 사용하는지 또는 모든 것이 메모리에서 발생하는지에 대해 상충되는 의견을 읽었습니다.
어떤 시나리오에서 다른 시나리오보다 성능이 우수합니까?
답변
tempdb를 사용하는 것은 그중 하나가 아니지만 아래 MSDN 링크에 설명 된 것처럼 Temporary Tables (#tmp)와 Table Variables (@tmp) 사이에는 약간의 차이가 있습니다.
경험상, 중소 규모의 데이터 및 간단한 사용 시나리오에는 테이블 변수를 사용해야합니다. (이것은 물론 많은 예외가있는 지나치게 광범위한 지침입니다-아래 및 다음 기사를 참조하십시오.)
그들 사이에서 선택할 때 고려해야 할 몇 가지 사항 :
-
임시 테이블은 실제 테이블이므로 CREATE INDEXes 등과 같은 작업을 수행 할 수 있습니다. 인덱스로 액세스하는 것이 더 빠른 대량의 데이터가있는 경우 임시 테이블이 좋은 옵션입니다.
-
테이블 변수는 PRIMARY KEY 또는 UNIQUE 제약 조건을 사용하여 인덱스를 가질 수 있습니다. 고유하지 않은 인덱스를 원하는 경우 고유 제한 조건에서 기본 키 열을 마지막 열로 포함하면됩니다. 고유 한 열이 없으면 ID 열을 사용할 수 있습니다. SQL 2014에는 고유하지 않은 인덱스도 있습니다. .
-
테이블 변수는 트랜잭션에 참여하지 않으며
SELECT로 암시 적으로NOLOCK있습니다. 트랜잭션 동작은 매우 유용 할 수 있습니다. 예를 들어, 프로 시저 중간에 롤백하려는 경우 해당 트랜잭션 중에 채워진 테이블 변수가 여전히 채워집니다! -
임시 테이블로 인해 저장 프로 시저가 종종 재 컴파일 될 수 있습니다. 테이블 변수는 그렇지 않습니다.
-
SELECT INTO를 사용하여 임시 테이블을 작성할 수 있습니다. 빠른 작성 (임시 쿼리에 적합)은 임시 테이블 구조를 미리 정의 할 필요가 없으므로 시간이 지남에 따라 변경되는 데이터 유형을 처리 할 수 있습니다.
-
함수에서 테이블 변수를 다시 전달하여 로직을 훨씬 쉽게 캡슐화하고 재사용 할 수 있습니다 (예 : 문자열을 임의의 구분 기호의 값 테이블로 분할하는 함수 만들기).
-
사용자 정의 함수 내에서 테이블 변수를 사용하면 해당 함수를보다 광범위하게 사용할 수 있습니다 (자세한 내용은 CREATE FUNCTION 설명서 참조). 함수를 작성하는 경우 특별한 요구가없는 한 임시 테이블에 테이블 변수를 사용해야합니다.
-
테이블 변수와 임시 테이블은 모두 tempdb에 저장됩니다. 그러나 테이블 변수 (2005 년 이후)는 tempdb ( ref ) 의 기본 데이터 정렬을 사용하는 임시 테이블과 현재 데이터베이스의 데이터 정렬을 기본값으로합니다 . 즉, 임시 테이블을 사용하고 db 데이터 정렬이 tempdb와 다른 경우 데이터 정렬 문제를 알고 있어야하며, 임시 테이블의 데이터와 데이터베이스의 데이터를 비교하려는 경우 문제가 발생합니다.
-
전역 임시 테이블 (## tmp)은 모든 세션과 사용자가 사용할 수있는 다른 유형의 임시 테이블입니다.
더 읽을 거리 :
-
dba.stackexchange.com에 대한 Martin Smith의 훌륭한 답변
-
이 두 가지 차이점에 대한 MSDN FAQ : https://support.microsoft.com/en-gb/kb/305977
-
MDSN 블로그 기사 : https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table
-
기사 : https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables
-
임시 테이블 및 임시 변수의 예기치 않은 동작 및 성능 영향 : Paulblog on SQLblog.com
답변
테이블 변수가 로깅에 참여하지 않는다는 허용 된 답변의 주장을 살펴보십시오.
로깅 양에 차이가 있다는 것은 일반적으로 사실이 아닙니다 (적어도 테이블 자체에 대한 insert/ update/ delete작업의 경우 추가 시스템 테이블로 인해 저장 프로 시저의 캐시 된 임시 객체에 대해 약간의 차이가 있음 을 발견 했지만) 업데이트).
다음 작업에 대한 a @table_variable및 #temp테이블에 대한 로깅 동작을 살펴 보았습니다 .
- 성공적인 삽입
- 제약 조건 위반으로 인해 명령문이 롤백되는 다중 행 삽입
- 최신 정보
- 지우다
- 할당 해제
트랜잭션 로그 레코드는 모든 작업에서 거의 동일했습니다.
테이블 변수 버전은 실제로 몇 가지가 추가 가 (에서 나중에 제거)에 추가 한 엔트리 얻을 수 있기 때문에 로그 항목을 sys.syssingleobjrefs기본 테이블을하지만, 전반적으로 약간 적은 바이트에 대한보다 테이블 변수가 소비의 내부 이름으로 순수 236 바이트 이하를 기록했다 #temp테이블 (118 자 이하 nvarchar).
전체 스크립트 재생성 (단일 사용자 모드에서 시작하고 모드를 사용하는 인스턴스에서 가장 잘 실행 됨 sqlcmd)
:setvar tablename "@T"
:setvar tablescript "DECLARE @T TABLE"
/*
--Uncomment this section to test a #temp table
:setvar tablename "#T"
:setvar tablescript "CREATE TABLE #T"
*/
USE tempdb
GO
CHECKPOINT
DECLARE @LSN NVARCHAR(25)
SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null)
EXEC(N'BEGIN TRAN StartBatch
SAVE TRAN StartBatch
COMMIT
$(tablescript)
(
[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,
InRowFiller char(7000) DEFAULT ''A'',
OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000)
)
BEGIN TRAN InsertFirstRow
SAVE TRAN InsertFirstRow
COMMIT
INSERT INTO $(tablename)
DEFAULT VALUES
BEGIN TRAN Insert9Rows
SAVE TRAN Insert9Rows
COMMIT
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns
BEGIN TRAN InsertFailure
SAVE TRAN InsertFailure
COMMIT
/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/
BEGIN TRY
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20
FROM sys.all_columns
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
BEGIN TRAN Update10Rows
SAVE TRAN Update10Rows
COMMIT
UPDATE $(tablename)
SET InRowFiller = LOWER(InRowFiller),
OffRowFiller =LOWER(OffRowFiller),
LOBFiller =LOWER(LOBFiller)
BEGIN TRAN Delete10Rows
SAVE TRAN Delete10Rows
COMMIT
DELETE FROM $(tablename)
BEGIN TRAN AfterDelete
SAVE TRAN AfterDelete
COMMIT
BEGIN TRAN EndBatch
SAVE TRAN EndBatch
COMMIT')
DECLARE @LSN_HEX NVARCHAR(25) =
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)
SELECT
[Operation],
[Context],
[AllocUnitName],
[Transaction Name],
[Description]
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
SELECT CASE
WHEN GROUPING(Operation) = 1 THEN 'Total'
ELSE Operation
END AS Operation,
Context,
AllocUnitName,
COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
COUNT(*) AS Cnt
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())
결과
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| | | | @TV | #TV | |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Operation | Context | AllocUnitName | Size in Bytes | Cnt | Size in Bytes | Cnt | Difference Bytes |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| LOP_ABORT_XACT | LCX_NULL | | 52 | 1 | 52 | 1 | |
| LOP_BEGIN_XACT | LCX_NULL | | 6056 | 50 | 6056 | 50 | |
| LOP_COMMIT_XACT | LCX_NULL | | 2548 | 49 | 2548 | 49 | |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust | 624 | 3 | 624 | 3 | |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust | 208 | 1 | 208 | 1 | |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | 832 | 4 | 832 | 4 | |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL | | 120 | 3 | 120 | 3 | |
| LOP_DELETE_ROWS | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 720 | 9 | 720 | 9 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysallocunits.clust | 444 | 3 | 444 | 3 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysallocunits.nc | 276 | 3 | 276 | 3 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.clst | 628 | 4 | 628 | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.nc | 484 | 4 | 484 | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysidxstats.clst | 176 | 1 | 176 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysidxstats.nc | 144 | 1 | 144 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysiscols.clst | 100 | 1 | 100 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysiscols.nc1 | 88 | 1 | 88 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysobjvalues.clst | 596 | 5 | 596 | 5 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrowsets.clust | 132 | 1 | 132 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrscols.clst | 528 | 4 | 528 | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.clst | 1040 | 6 | 1276 | 6 | 236 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1 | 820 | 6 | 1060 | 6 | 240 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2 | 820 | 6 | 1060 | 6 | 240 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc3 | 480 | 6 | 480 | 6 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syssingleobjrefs.clst | 96 | 1 | | | -96 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syssingleobjrefs.nc1 | 88 | 1 | | | -88 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | Unknown Alloc Unit | 72092 | 19 | 72092 | 19 | |
| LOP_DELETE_ROWS | LCX_TEXT_MIX | Unknown Alloc Unit | 16348 | 37 | 16348 | 37 | |
| LOP_FORMAT_PAGE | LCX_HEAP | Unknown Alloc Unit | 1596 | 19 | 1596 | 19 | |
| LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 252 | 3 | 252 | 3 | |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 84 | 1 | 84 | 1 | |
| LOP_FORMAT_PAGE | LCX_TEXT_MIX | Unknown Alloc Unit | 4788 | 57 | 4788 | 57 | |
| LOP_HOBT_DDL | LCX_NULL | | 108 | 3 | 108 | 3 | |
| LOP_HOBT_DELTA | LCX_NULL | | 9600 | 150 | 9600 | 150 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysallocunits.clust | 456 | 3 | 456 | 3 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.syscolpars.clst | 644 | 4 | 644 | 4 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysidxstats.clst | 180 | 1 | 180 | 1 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysiscols.clst | 104 | 1 | 104 | 1 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysobjvalues.clst | 616 | 5 | 616 | 5 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrowsets.clust | 136 | 1 | 136 | 1 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrscols.clst | 544 | 4 | 544 | 4 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysschobjs.clst | 1064 | 6 | 1300 | 6 | 236 |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.syssingleobjrefs.clst | 100 | 1 | | | -100 |
| LOP_INSERT_ROWS | LCX_CLUSTERED | Unknown Alloc Unit | 135888 | 19 | 135888 | 19 | |
| LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 1596 | 19 | 1596 | 19 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysallocunits.nc | 288 | 3 | 288 | 3 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.syscolpars.nc | 500 | 4 | 500 | 4 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysidxstats.nc | 148 | 1 | 148 | 1 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysiscols.nc1 | 92 | 1 | 92 | 1 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc1 | 844 | 6 | 1084 | 6 | 240 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc2 | 844 | 6 | 1084 | 6 | 240 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc3 | 504 | 6 | 504 | 6 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.syssingleobjrefs.nc1 | 92 | 1 | | | -92 |
| LOP_INSERT_ROWS | LCX_TEXT_MIX | Unknown Alloc Unit | 5112 | 71 | 5112 | 71 | |
| LOP_MARK_SAVEPOINT | LCX_NULL | | 508 | 8 | 508 | 8 | |
| LOP_MODIFY_COLUMNS | LCX_CLUSTERED | Unknown Alloc Unit | 1560 | 10 | 1560 | 10 | |
| LOP_MODIFY_HEADER | LCX_HEAP | Unknown Alloc Unit | 3780 | 45 | 3780 | 45 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.syscolpars.clst | 384 | 4 | 384 | 4 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysidxstats.clst | 100 | 1 | 100 | 1 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust | 92 | 1 | 92 | 1 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst | 1144 | 13 | 1144 | 13 | |
| LOP_MODIFY_ROW | LCX_IAM | Unknown Alloc Unit | 4224 | 48 | 4224 | 48 | |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 13632 | 169 | 13632 | 169 | |
| LOP_MODIFY_ROW | LCX_TEXT_MIX | Unknown Alloc Unit | 108640 | 120 | 108640 | 120 | |
| LOP_ROOT_CHANGE | LCX_CLUSTERED | sys.sysallocunits.clust | 960 | 10 | 960 | 10 | |
| LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 1200 | 20 | 1200 | 20 | |
| LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 1080 | 18 | 1080 | 18 | |
| LOP_SET_BITS | LCX_SGAM | Unknown Alloc Unit | 120 | 2 | 120 | 2 | |
| LOP_SHRINK_NOOP | LCX_NULL | | | | 32 | 1 | 32 |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Total | | | 410144 | 1095 | 411232 | 1092 | 1088 |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
답변
어떤 시나리오에서 다른 시나리오보다 성능이 우수합니까?
작은 테이블 (1000 행 미만)의 경우 임시 변수를 사용하고, 그렇지 않으면 임시 테이블을 사용하십시오.
답변
@wcm-실제로 테이블 변수를 선택하는 것은 Ram에만 해당되는 것이 아니며 디스크에 부분적으로 저장할 수 있습니다.
임시 테이블에는 인덱스가있을 수 있지만 테이블 변수에는 기본 인덱스 만있을 수 있습니다. 속도가 문제인 경우 테이블 변수가 더 빠를 수 있지만 많은 레코드가 있거나 클러스터 된 인덱스의 임시 테이블을 검색해야하는 경우 임시 테이블이 더 좋습니다.
답변
-
임시 테이블 : 임시 테이블은 데이터를 작성하고 백업하기 쉽습니다.
테이블 변수 : 그러나 테이블 변수는 보통 일반 테이블을 만들 때의 노력과 관련이 있습니다.
-
임시 테이블 : 임시 테이블 결과는 여러 사용자가 사용할 수 있습니다.
테이블 변수 : 그러나 테이블 변수는 현재 사용자 만 사용할 수 있습니다.
-
임시 테이블 : 임시 테이블은 tempdb에 저장됩니다. 네트워크 트래픽이 발생합니다. 임시 테이블에 큰 데이터가 있으면 데이터베이스에서 작동해야합니다. 성능 문제가 존재합니다.
테이블 변수 : 그러나 테이블 변수는 일부 데이터의 실제 메모리에 저장되며 나중에 크기가 증가하면 tempdb로 이동합니다.
-
임시 테이블 : 임시 테이블은 모든 DDL 작업을 수행 할 수 있습니다. 인덱스 생성, 삭제, 변경 등을 허용합니다.
테이블 변수 : 테이블 변수는 DDL 작업을 수행 할 수 없습니다. 그러나 테이블 변수를 사용하면 클러스터형 인덱스 만 만들 수 있습니다.
-
임시 테이블 : 임시 테이블은 현재 세션 또는 전역에 사용할 수 있습니다. 따라서 여러 사용자 세션이 표의 결과를 활용할 수 있습니다.
테이블 변수 : 그러나 테이블 변수는 해당 프로그램까지 사용할 수 있습니다. (저장 절차)
-
임시 테이블 : 임시 변수는 트랜잭션을 사용할 수 없습니다. 임시 테이블을 사용하여 DML 작업을 수행하면 롤백되거나 트랜잭션을 커밋 할 수 있습니다.
테이블 변수 : 그러나 테이블 변수에는 사용할 수 없습니다.
-
임시 테이블 : 함수는 임시 변수를 사용할 수 없습니다. 또한 함수에서 DML 작업을 수행 할 수 없습니다.
테이블 변수 : 그러나이 함수를 통해 테이블 변수를 사용할 수 있습니다. 그러나 테이블 변수를 사용하면 그렇게 할 수 있습니다.
-
임시 테이블 : 모든 후속 호출에 대해 임시 변수를 사용할 때 저장 프로시 저는 재 컴파일을 수행합니다 (동일한 실행 계획을 사용할 수 없음).
테이블 변수 : 테이블 변수는 그렇지 않습니다.
답변
임시 변수가 메모리에만 있다는 신화를 믿는 모든 사람들에게
먼저, 테이블 변수가 반드시 메모리 상주 인 것은 아닙니다. 메모리 부족 상황에서 테이블 변수에 속하는 페이지를 tempdb로 푸시 아웃 할 수 있습니다.
기사를 읽으십시오 : TempDB :: 테이블 변수 대 로컬 임시 테이블
답변
다른 주요 차이점은 테이블 변수에는 임시 테이블처럼 열 통계가 없다는 것입니다. 이는 쿼리 옵티마이 저가 테이블 변수에 몇 개의 행이 있는지 알지 못하므로 (추정 1) 테이블 변수에 실제로 많은 수의 행이있는 경우 최적화되지 않은 계획이 생성 될 수 있습니다.
