[MSSQL] 임시테이블(@테이블,#테이블)

 

1. 임시 테이블 vs 테이블 변수

	임시 테이블(#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 컬럼을 지정해서 데이터가 정렬된 형태로 저장하여 사용
사용 예	* 생성 CREATE TABLE dbo.#Table (     A INT IDENTITY(1,1),      B INT ) * 입력  INSERT INTO dbo.#Table VALUES(1) * 검색  SELECT * FROM dbo.#Table	* 생성(선언) DECLARE @T table (     OrderID int identity(1,1) PRIMARY KEY NONCLUSTERED,      OrderDate datetime CHECK(OrderDate >= '1900-01-01') )  * 입력 INSERT INTO @T SELECT TOP 10 OrderDate FROM Orders    * 사용 SELECT * FROM @T   UPDATE @T SET OrderDate = OrderDate + 1   DELETE @T WHERE OrderDate > '1996-07-10'   SELECT * FROM @T   GO

 

2. 임시 테이블 상세 설명

(1) 설명

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

- 사용자가 연결이 끊겼을 때 임시 테이블이 삭제되지 않는 경우 SQL Server는 자동으로 임시 테이블을 삭제한다.

- 현재 설정된 데이터베이스에 저장되지 않고 시스템 데이터베이스 Tempdb에 저장된다. 단 데이터 건수가 작을 때는 메모리에만 존재 Tempdb 사이즈 증가 안 함(데이터가 29K 이하 일 때는 메모리에 존재)

 

(2) 형태

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

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

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

 

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

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

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

 

(3) 장점

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

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

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

④ ALTER TABLE이 가능하다.

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

 

3. 테이블 변수

(1) 설명

- SQL Server 2000 버전에서 새로 추가된 형식으로 임시 테이블을 만들어 사용하는 것과 비슷한 역할을 수행함.

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

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

 

(2) 장점

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

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

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

 

(3) 제약사항

① FK를 사용할 수 없다.

② ALTER TABLE를 할 수 없다.

③ 추가 인덱스를 선언할 수 없다. 그러나 PK, UNIQUE가 있다.

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