SQL Server의 임시 테이블과 테이블 변수의 차이점은 무엇입니까?
SQL Server 2005에서는 다음 두 가지 방법 중 하나를 사용하여 임시 테이블을 만들 수 있습니다.
declare @tmp table (Col1 int, Col2 int);
또는
create table #tmp (Col1 int, Col2 int);
이 둘의 차이점은 무엇입니까?@tmp가 아직 tempdb를 사용하고 있는지, 모든 것이 메모리에서 발생하고 있는지에 대해 의견이 엇갈리는 것을 보았습니다.
어떤 시나리오가 다른 시나리오보다 성능이 우수합니까?
Temporary Tables(#tmp)와 Table Variables(@tmp) 사이에는 몇 가지 차이점이 있지만 tempdb를 사용하는 것은 다음 MSDN 링크에서 설명하듯이 그 중 하나가 아닙니다.
경험적으로 소량에서 중량의 데이터 볼륨 및 간단한 사용 시나리오의 경우 테이블 변수를 사용해야 합니다.(물론 많은 예외가 있는 지나치게 광범위한 가이드라인입니다.아래와 다음 기사를 참조하십시오.)
둘 중 하나를 선택할 때 고려해야 할 몇 가지 사항은 다음과 같습니다.
임시 테이블은 실제 테이블이므로 CREATE INDEXes 등의 작업을 수행할 수 있습니다.인덱스별 액세스 속도가 빠른 대량의 데이터가 있는 경우 임시 테이블을 사용하는 것이 좋습니다.
테이블 변수는 PRIMAY KEY 또는 UNIQUIRE 제약 조건을 사용하여 인덱스를 가질 수 있습니다.(고유하지 않은 인덱스를 원하는 경우 고유 제약조건의 마지막 열로 프라이머리 키 열을 포함시키면 됩니다.원하는 열이 없는 경우 ID 열을 사용할 수 있습니다.)SQL 2014에는 고유하지 않은 인덱스도 있습니다.
및 에 참여하지
SELECT으로 「」를 사용하고 있습니다.NOLOCK트랜잭션 동작은 매우 도움이 됩니다.예를 들어, 중간에 롤백을 하고 싶은 경우 해당 트랜잭션 중에 입력된 테이블 변수가 채워집니다.임시 테이블로 인해 저장 프로시저가 재컴파일되는 경우가 종종 있습니다.테이블 변수는 그렇지 않습니다.
SELECT INTO를 사용하여 임시 테이블을 만들 수 있습니다. 임시 테이블 구조를 미리 정의할 필요가 없기 때문에 쓰기 속도가 빨라지고(애드혹 쿼리에 적합), 시간에 따른 데이터 유형 변경에 대처할 수 있습니다.
함수에서 테이블 변수를 전달하여 로직을 캡슐화하고 재사용하기 쉽게 할 수 있습니다(예를 들어 임의의 딜리미터로 문자열을 값의 테이블로 분할하는 함수를 만듭니다).
사용자 정의 함수에서 테이블 변수를 사용하면 이러한 함수를 더 폭넓게 사용할 수 있습니다(자세한 내용은 CREATE FUNTION 설명서 참조).함수를 작성할 경우 특별한 필요가 없는 한 임시 테이블 위에 테이블 변수를 사용해야 합니다.
테이블 변수와 임시 테이블은 모두 tempdb에 저장됩니다.그러나 테이블 변수(2005년 이후)는 현재 데이터베이스와 tempdb(ref)의 기본 조회를 취하는 임시 테이블의 조회가 기본값이 됩니다.즉, 임시 테이블을 사용하고 DB 조회가 tempdb와 다르면 데이터 조회가 문제임을 알아야 합니다.따라서 임시 테이블의 데이터와 데이터베이스 내의 데이터를 비교하려는 경우 문제가 발생합니다.
글로벌 임시 테이블(##tmp)은 모든 세션과 사용자가 사용할 수 있는 다른 유형의 임시 테이블입니다.
기타 참고 자료:
MSDN의 차이점에 관한 FAQ: https://support.microsoft.com/en-gb/kb/305977
MDSN 블로그 기사 https://learn.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
임시 테이블 및 임시 변수의 예상치 못한 동작 및 성능에 미치는 영향: SQLblog.com의 Paul White
승인된 답변에서 테이블 변수가 로깅에 참여하지 않는다는 주장만 보면 됩니다.
로깅 양에 차이가 있다는 것은 일반적으로 사실이 아닌 것 같습니다(적어도insert/update/delete그 후 시스템 테이블 갱신이 추가되었기 때문에 스토어드 프로시저에서 캐시된 임시 객체에 대해 약간의 차이가 있음을 알 수 있었습니다.)
로깅 .@table_variable a. a. a.#temp하다
- 삽입 성공
- 다중 행 제약 조건 위반으로 인해 문이 롤백된 위치 삽입.
- 갱신하다
- 삭제
- 할당 해제
트랜잭션 로그 레코드는 모든 작업에 대해 거의 동일했습니다.
테이블 변수 버전에는 실제로 몇 개의 추가 로그엔트리가 있습니다.이는 엔트리가 추가(나중에 삭제)되기 때문입니다.sys.syssingleobjrefs의 내부 에 base table 하게 몇 적게 기록되었습니다.#temp less ')( ' ' ' ')')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뿐만이 아닙니다.디스크에 부분적으로 저장할 수 있습니다.
임시 테이블에는 인덱스를 사용할 수 있지만 테이블 변수에는 프라이머리 인덱스만 사용할 수 있습니다.속도가 문제인 경우 테이블 변수가 더 빠를 수 있지만 레코드가 많거나 클러스터된 인덱스의 임시 테이블을 검색해야 하는 경우에는 온도 테이블을 사용하는 것이 좋습니다.
Temp table: Temp table은 데이터를 쉽게 만들고 백업할 수 있습니다.
테이블 변수:그러나 테이블 변수에는 통상적인 테이블을 작성할 때의 노력이 포함됩니다.
임시 테이블:임시 테이블 결과는 여러 사용자가 사용할 수 있습니다.
테이블 변수:그러나 테이블 변수는 현재 사용자만 사용할 수 있습니다.
임시 테이블:Temp 테이블은 tempdb에 저장됩니다.네트워크 트래픽이 생성됩니다.임시 테이블에 대용량 데이터가 있는 경우 데이터베이스 전체에서 작동해야 합니다.퍼포먼스 문제가 발생합니다.
테이블 변수:그러나 테이블 변수는 일부 데이터의 물리 메모리에 저장되며 나중에 크기가 증가하면 tempdb로 이동합니다.
임시 테이블:임시 테이블은 모든 DDL 작업을 수행할 수 있습니다.인덱스 작성, 폐기, 변경 등이 가능합니다.
테이블 변수:반면 테이블 변수는 DDL 작업을 수행할 수 없습니다.그러나 테이블 변수를 사용하면 클러스터된 인덱스만 생성할 수 있습니다.
임시 테이블:임시 테이블은 현재 세션 또는 글로벌에 사용할 수 있습니다.여러 사용자 세션이 표의 결과를 활용할 수 있도록 합니다.
테이블 변수:그러나 테이블 변수는 해당 프로그램까지 사용할 수 있습니다.(저장 절차)
임시 테이블:Temp 변수는 트랜잭션을 사용할 수 없습니다.임시 테이블을 사용하여 DML 작업을 수행하면 롤백되거나 트랜잭션을 커밋할 수 있습니다.
테이블 변수:그러나 테이블 변수에는 할 수 없습니다.
임시 테이블:함수는 온도 변수를 사용할 수 없습니다.게다가 함수에서는 DML 연산을 할 수 없습니다.
테이블 변수:그러나 이 함수는 테이블 변수를 사용할 수 있게 해줍니다.그러나 테이블 변수를 사용하면 가능합니다.
임시 테이블:모든 서브시퀀스 호출에 대해 temp 변수를 사용하면 스토어드 프로시저는 재컴파일(같은 실행 계획을 사용할 수 없습니다)을 수행합니다.
테이블 변수:반면 테이블 변수는 그렇게 하지 않습니다.
온도 변수가 기억에만 있다는 속설을 믿는 여러분들을 위해
첫째, 테이블 변수가 반드시 메모리 상주인 것은 아닙니다.메모리 압력 하에서 테이블 변수에 속하는 페이지는 tempdb에 푸시할 수 있다.
다음 기사를 읽어보세요.TempDB:: 테이블 변수와 로컬 임시 테이블
인용: Professional SQL Server 2012 내부 및 트러블 슈팅
통계 임시 테이블과 표 변수의 주요 차이점은 통계량이 표 변수에 대해 생성되지 않는다는 것입니다.여기에는 두 가지 주요 결과가 있습니다. 쿼리 옵티마이저는 포함된 데이터에 관계없이 테이블 변수 내의 행 수에 대해 fied 추정치를 사용합니다.또한 데이터를 추가하거나 제거해도 추정치는 변경되지 않습니다.
색인 제약 조건을 만들 수 있지만 테이블 변수에 색인을 만들 수 없습니다.즉, 기본 키 또는 고유한 제약 조건을 작성하면 테이블 변수에 인덱스를 작성할 수 있습니다(제한 조건을 지원하기 위해 작성됨).제약 조건이 있고 따라서 통계가 있는 인덱스가 있더라도 쿼리가 컴파일될 때 인덱스는 사용되지 않습니다.왜냐하면 인덱스는 컴파일 시 존재하지 않으며 재컴파일도 발생하지 않기 때문입니다.
스키마 수정 스키마 수정은 임시 테이블에서 가능하지만 테이블 변수에서는 가능하지 않습니다.스키마 변경은 임시 테이블에서도 가능하지만 테이블을 사용하는 스테이트먼트의 재컴파일 원인이 되므로 사용을 피하십시오.
메모리에 테이블 변수가 생성되지 않음
테이블 변수가 메모리 내 구조이기 때문에 임시 테이블보다 더 빨리 처리된다는 오해가 흔히 있습니다.세션별 tempdb 사용현황을 보여주는 sys . dm _ db _ session _ space _ usage라는DMV 덕분에 그렇지 않음을 증명할 수 있습니다.SQL Server를 재시작하여 DMV를 클리어한 후 다음 스크립트를 실행하여 세션 ID가 사용자 객체 할당 페이지 수에 대해 0을 반환하도록 설정합니다.
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
이제 다음 스크립트를 실행하여 1개의 컬럼으로 임시 테이블을 만들고 1개의 행으로 채우는 것으로 임시 테이블이 얼마나 많은 공간을 사용하는지 확인할 수 있습니다.
CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
서버의 결과는 테이블이 tempdb에서1페이지 할당되어 있는 것을 나타내고 있습니다.이번에는 같은 스크립트를 실행하지만 테이블 변수를 사용합니다.
DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
어떤 것을 사용할까요?
임시 테이블 또는 테이블 변수를 사용할지 여부는 철저한 테스트를 통해 결정해야 하지만, 문제가 발생할 수 있는 항목이 훨씬 적기 때문에 기본적으로 임시 테이블을 사용하는 것이 좋습니다.
고객이 적은 수의 행을 처리하기 때문에 테이블 변수를 사용하여 코드를 개발하는 것을 본 적이 있습니다. 임시 테이블보다 몇 년 후에는 테이블 변수에 수십만 개의 행이 있고 성능이 형편없기 때문에 결정을 내릴 때 용량 계획을 고려해 보십시오.
다른 주요 차이점은 표 변수에는 임시 표와 같이 열 통계량이 없다는 것입니다.즉, 쿼리 최적기가 표 변수에 몇 개의 행이 있는지 알 수 없으므로(추측 1), 표 변수가 실제로 많은 수의 행을 가질 경우 매우 비최적 계획이 생성될 수 있습니다.
또 다른 차이점:
테이블 var에는 테이블 var를 작성하는 프로시저 내의 스테이트먼트에서만 액세스할 수 있습니다.이 프로시저에 의해 호출된 다른 프로시저 또는 네스트된 다이내믹 SQL(exec 또는 sp_executesql 경유)에서는 액세스할 수 없습니다.
반면 임시 테이블의 범위에는 호출된 프로시저와 중첩된 동적 SQL에 코드가 포함됩니다.
절차에 의해 작성된 테이블에 다른 호출된 프로시저 또는 동적 SQL에서 액세스할 수 있어야 하는 경우 임시 테이블을 사용해야 합니다.이것은 복잡한 상황에서 매우 편리할 수 있습니다.
이 두 가지 주요 차이점은 임시 테이블은 병렬 삽입을 지원하지만 테이블 변수는 지원하지 않는다는 것입니다.실행 계획과의 차이를 알 수 있을 것입니다.채널 9와 MSDN 문서에 관한 SQL 워크숍 비디오입니다.
또한 SQLMenace가 이전에 응답한 바와 같이 작은 테이블에 테이블 변수를 사용해야 하는 이유, 그렇지 않으면 임시 테이블을 사용해야 합니다.
「 」의 Temporary Tables (##temp/#temp) ★★★★★★★★★★★★★★★★★」Table Variables (@table)음음음같 뭇매하다
Table variable (@table)되어 있습니다.memorya, a, 입니다.Temporary table (##temp/#temp)되어 있습니다.tempdb database그러나 메모리 압력이 있을 경우 테이블 변수에 속한 페이지가 tempdb로 푸시될 수 있습니다.Table variables할 수 없다transactions, logging or locking★★★★★★★★★★★★★★★★★★★.@table faster then #temp따라서 테이블 변수가 임시 테이블보다 빠릅니다.Temporary table는 스키마를 할 수 .Table variables.Temporary tables생성된 루틴과 하위 루틴에도 표시됩니다.반면 테이블 변수는 작성된 루틴에서만 표시됩니다.Temporary tablesCREATE INDEXes'아예'입니다.Table variables않다CREATE INDEX를 사용할 수 .Primary Key or Unique Constraint.
또한 두 테이블을 모두 더 빠른 파생 테이블로 대체할 수도 있습니다.그러나 모든 퍼포먼스 튜닝과 마찬가지로 실제 데이터에 대한 실제 테스트만이 특정 쿼리에 대한 최선의 접근방식을 알려줄 수 있습니다.
SQL에서 Temporary 테이블은 TempDB에 저장되며 로컬 임시 테이블은 현재 세션에서만 표시되며 다른 세션에서는 표시되지 않습니다.이는 네스트된 스토어드 프로시저 호출 간에 공유할 수 있습니다.Global Temporary 테이블은 다른 모든 세션에 표시되며 마지막 연결 참조 테이블이 닫힙니다.예를들면,
Select Dept.DeptName, Dept.DeptId, COUNT(*) as TotalEmployees
into #TempEmpCount
from Tbl_EmpDetails Emp
join Tbl_Dept Dept
on Emp.DeptId = Dept.DeptId
group by DeptName, Dept.DeptId
테이블 변수는 tempTables와 비슷하며 테이블 변수도 TempDB에 생성됩니다.테이블 변수의 범위는 선언된 배치, 저장 프로시저 또는 문 블록입니다.프로시저간의 파라미터로서 전달할 수 있습니다.테이블 변수를 사용하여 동일한 쿼리를 작성할 수 있습니다.
Declare @tblEmployeeCount table
(DeptName nvarchar(20),DeptId int, TotalEmployees int)
Insert @tblEmployeeCount
Select DeptName, Tbl_Dept.DeptId, COUNT(*) as TotalEmployees
from Tbl_EmpDetails
join Tbl_Dept
on Tbl_EmpDetails.DeptId = Tbl_Dept.DeptId
group by DeptName, Tbl_Dept.DeptId
언급URL : https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server
'programing' 카테고리의 다른 글
| SQL Server 문자열에서 최신 변환 (0) | 2023.04.07 |
|---|---|
| 기존 열의 기본값을 설정하는 방법 (0) | 2023.04.07 |
| T-SQL을 사용하여 하위 문자열의 마지막 항목 색인 찾기 (0) | 2023.04.07 |
| SQL Server에서 실행 중인 쿼리 나열 (0) | 2023.04.07 |
| 잭슨:각 값에 대해 올바른 유형의 맵으로 역직렬화 (0) | 2023.04.02 |