MSSQL - 임시테이블과 테이블 변수 비교

* 결론 :

	임시테이블	테이블 변수
존재 기간	명시적으로 삭제를 안할 시에는 세션 연결 기간동안 존재	명시적으로 삭제를 안할 시에는 배치 처리기간 동안 존재
저장소	Tempdb	Tempdb
쿼리비용	대용량에서는 쿼리 비용 유리	소용량에서 쿼리 비용 유리
장단점	대용량 데이터 처리에 유리 세션 단위 처리시에 사용	소용량 데이터 처리에서 유리 저장 프로시져에서 테이블 변수 사용하면 임시 테이블 사용할 떄보다 저장 프로시저를 다시 컴파일하는 일이 줄어듬. 배치 처리 단위인 저장 프로시져에서 사용 유리

 

 

 

1.임시테이블

---

 

1) 임시테이블의 테이블 이름은 숫자 기호(#)으로 시작한다.

 

-임시테이블은 현재 데이터베이스에 저장되지 않고 시스템 데이터베이스(tempdb)에 저장

(단, 데이터가 29K 이하 일 때에는 메모리에 존재하여 tempdb 사이즈는 증가하지 않는다.)

 

-임시테이블에도 지역 / 전역으로 구분될 수 있으며 지역 임시 테이블은 #, 전역임시테이블일 경우 ##을 붙여 임시테이블로 표현된다.

 

2) 임시테이블 생성

 

- 임시테이블은 연결(세션)이 끊기면 자동으로 사라진다.(Drop)

 

 

3) 임시테이블의 장점 :

①     인덱스를 작성할 수 있다.

②     FK(외래키)를 제외한 나머지 제약을 지정할 수 있다.

③     테이블은 작성이 되지만 경고 메시지와 함께 FK 선언은 제외된다.

④     ALTER TABLE이 가능하다.

⑤      INSERT INTO, BULK INSERT 문과 함께 사용할 수 있다.

 

 

2. 테이블 변수

---

 

1) 변수

테이블 변수를 설명하기 전에 MSSQL 에서의 '변수'에 대해 알아보면 타 프로그래밍 언어와 유사하게 값을 담아두는 것이라고 보면 된다.

 

사용 방법:

 

 

 

 

 

 

2) SQL Server 2000 버전에서 새로 추가된 형식. 임시 테이블 사용과 유사한 역할

  테이블 변수에서는 단순히 값 뿐만이 아니라 여러행으로 떨어지는 Select의 결과를 담아 둘 수 있다.

  이를 사용하면 쿼리의 복잡성을 떨어트리고, 커서를 사용하는 것에 비해 속도 개선에 많은 도움이 된다.

 

 

 

3) 테이블 변수의 특징

 

A.     테이블 변수는 테이블 변수가 정의된 함수 및 저장 프로시져 및 일괄 처리가 끝나면 자동으로 정리됨.

B.      테이블 변수와 관련된 트랜잭션은 테이블 변수가 업데이트 되는 동안만 지속됨. 따라서 테이블 변수를 사용하면 리소스 잠금과 로깅에 대한 요구가 줄어듬.

4)    장점

A.     Primary Key(기본키), UNIQUE(유일키), CHECK 제약을 쓸 수 있다.

B.      IDENTITY(식별자) 속성을 지정할 수 있다.

C.      SELECT, INSERT, UPDATE, DELETE 문에 사용할 수 있다.

 

5)    제약사항

 

A.     FK를 사용할 수 없다.

B.      ALTER TABLE를 할 수 없다.

C.      추가 인덱스를 선언할 수 없다. (클러스터 인덱스는 가질 수 있다)

D.     그러나 PK, UNIQUE가 있다.

E.      로컬 변수나, UDF 안에서만 사용된다.

F.      임시 테이블의 테이블 이름은 숫자 기호(#)로 시작합니다.