[MSSQL]임시 테이블과 테이블 변수 비교

임시 테이블

임시 테이블 설명

• 임시 테이블의 테이블 이름은 숫자 기호(#)로 시작한다.
• 사용자가 연결이 끊겼을 때, 임시 테이블이 삭제되지 않는 경우, SQL Server는 자동으로 임시 테이블을 삭제한다.
• 현재 설정된 데이터베이스에 저장되지 않고, 시스템 데이터베이스 Tempdb에 저장된다.

※단 데이터 건수가 적을 때(29K 이하)는 메모리에만 존재하고 Tempdb사이즈는 증가하지 않는다.

 

임시 테이블 형태

로컬 임시 테이블(Local Temporary Table)

- 하나의 숫자 기호(#)로 시작함.

- 이 테이블은 테이블을 만든 연결에서만 볼 수 있음.

 

전역 임시 테이블(Global Temporary Table)

- 두 개의 숫자 기호(##)으로 시작함.

- 이 테이블을 만든 연결이 끊어지기 전에 이 테이블이 명시적으로 삭제되지 않으며, 다른 모든 작업이 이 테이블에 대한 참조를 중지할 때 바로 삭제된다.

 

임시 테이블 장점

• 인덱스를 작성할 수 있다.
• FK(외래 키)를 제외한 나머지 제약을 지정할 수 있다.
• 테이블은 작성이 되지만, 경고 메시지와 함께 FK 선언은 제외된다.
• ALTER TABLE이 가능하다.
• INSERT INTO, BULK INSERT 문과 함께 사용할 수 있다.

---

테이블 변수

테이블 변수 설명

• SQL Server 2000 버전에서 새로 추가된 형식으로 임시 테이블을 만들어 사용하는 것과 비슷한 역할을 수행한다.
• 테이블 변수는 변수가 정의된 함수 및 저장 프로시저 및 일괄 처리가 끝나면 자동으로 정리된다.
• 테이블 변수와 관련된 트랜잭션은 테이블 변수가 업데이트되는 동안만 지속되기 때문에 테이블 변수를 사용하면 리소스 잠금과 로깅에 대한 요구가 줄어든다.

 

테이블 변수 장점

• Primary Key(기본키), UNIQUE(유일 키), CHECK 제약을 쓸 수 있다.
• IDENTITY(식별자) 속성을 지정할 수 있다.
• SELECT, INSERT, UPDATE, DELETE 문에 사용할 수 있다.

 

테이블 변수 제약사항

• FK(외래키)를 사용할 수 없다.
• ALTER TABLE를 할 수 없다.
• 추가 인덱스를 선언할 수 없다. 그러나 PK, UNIQUE가 있다.
• 로컬 변수나 UDF 안에서만 사용된다.

---

임시 테이블, 테이블 변수 비교

 

임시 테이블과 테이블 변수의 공통점

• 여러 단계를 거쳐 동일한 데이터에 반복 작업을 해결할 때
• 복잡한 쿼리를 단순화시킬 때
• 서버상의 커서(Cursor) 사용으로 인한 부하를 줄이고자 할 때

 

임시 테이블과 테이블 변수의 차이점

	임시 테이블(#Table)	테이블 변수(@Table)
존재 기간	명시적으로 삭제를 안할 시, 세션 연결 기간동안 존재	명시적으로 삭제를 안할 시, 배치 처리기간 동안 존재
저장소 쿼리 비용	Tempdb	Tempdb
장점	- 통계정보 생성으로 대량 데이터 사용시 조회 성능이 빠름 - NON CLUSTER INDEX 사용으로 임시 테이블을 이용한 조인(Join)시 쿼리 성능을 높일 수 있음	- Transaction Overhead가 없음 - Lock Overhead가 없음 - Rollback Overhead가 없음 - 저장 프로시저 안에서 사용시, 저장 프로시저 재컴파일 없음
단점	- Transaction Overhead가 있음 - Lock Overhead가 있음 - Rollback Overhead가 있음 - 저장 프로시저 안에서 사용시, 저장 프로시저 실행시마다 임시 테이블 사용구문에 대한 재컴파일 발생(CPU 부하)	- PK, UNIQUE 옵션을 통한 CLUSTER INDEX만 생성 가능 - 테이블의 각 컬럼에 대한 통계 정보가 생성되지 않음 - 100건 이상 대량 테이터에서는 Sort작업 등으로 인한 성능 저하 발생
사용 Case	- INSERT되는 데이터가 100건 이상인 경우	- INSERT되는 데이터가 100건 이하, 1~2개 컬럼 사용시 적합 - 가능하면 PK 또는 UNIQUE컬럼을 지정해서 데이터가 정렬된 형태로 저장하여 사용

---

여담

SQL문을 사용하여 필요한 지표나 쿼리를 만들 때, 임시 테이블이나 테이블 변수를 사용하는 일이 빈번하게 된다.

처음엔 두 가지 다 사용하여 보고 어떤 경우는 느려지고 어떤 경우는 제대로 성능이 발휘한다.

그러다 보니, 의문이 생겼다. 어떤 경우에는 임시 테이블을 사용하고 어떤 경우에는 테이블 변수를 사용해야 하는지.

그러한 의문이 쌓이면서 호기심이 생기고 궁금해져서 이리저리 찾아보고 정리하였다.

남을 위하기보다는 나를 위하여 더 많은 것을 알아가고자 하고 있다.

그러다 보면 남이 정리해둔 방식을 직접 적어보면서 기억에 남게 된다.

그러고 설명이 부족한 부분에 대하여 더 확인하고자 하게 된다.

---

참고

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=islove8587&logNo=220608680181 

[MSSQL] 임시 테이블 vs 테이블 변수