본문 바로가기

SQL Server/SQL Server Tip & Tech

테이블 변수 vs 임시 테이블

테이블 변수와 임시 테이블을 비교한 좋은 글

원문

Comparing Table Variables with Temporary Tables
By Wayne Sheffield

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/


테이블 변수와 임시 테이블은 공통된 성격으로 인해 둘 중 하나를 선택해 사용해야하는 경우가 많은데, 어느 한쪽이 절대적으로 좋고 한쪽은 나쁘다라고 결론 지을 수 없다.

중요한 것은 둘의 차이를 정확히 이해하는 것. 그리고 어느 한쪽을 선택할 때는 충분한 테스트가 필요하다는 것.

그리고 미신을 믿지 말자.

미신 1
테이블 변수는 메모리에 위치한다.

미신 2
임시 테이블은 항상 물리 I/O가 발생한다.

미신 3
테이블 변수는 어떤 인덱스도 가질 수 없다.

Feature
Table Variables Temporary Tables
Scope Current batch Current session, nested stored procedures. Global: all sessions.
Usage UDFs, Stored Procedures, Triggers, Batches. Stored Procedures, Triggers, Batches.
Creation DECLARE statement only. CREATE TABLE statement.
SELECT INTO statement.
Table name Maximum 128 characters. Maximum 116 characters.
Column data types Can use user-defined data types. User-defined data types and XML collections must be in tempdb to use.
Can use XML collections.
Collation String columns inherit collation from current database. String columns inherit collation from tempdb database.
Indexes Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes can be added after the table has been created.
Constraints PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed. PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns) Statements are not allowed. Statements are allowed.
Data insertion INSERT statement (SQL 2000: cannot use INSERT/EXEC). INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT). The SET IDENTITY_INSERT statement is not supported. The SET IDENTITY_INSERT statement is supported.
Truncate table Not allowed. Allowed.
Destruction Automatically at the end of the batch. Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions Last only for length of update against the table variable. Uses less than temporary tables. Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations Not applicable. Creating temp table and data inserts cause procedure recompilations.
Rollbacks Not affected (Data not rolled back). Affected (Data is rolled back).
Statistics Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans. Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures SQL 2008 only, with predefined user-defined table type. Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints). Not allowed. Allowed, but be aware of multi-user issues.
Dynamic SQL Must declare table variable inside the dynamic SQL. Can use temporary tables created prior to calling the dynamic sql.