MSSQL 임시테이블 # ##, 테이블변수 @ 차이와 사용방법

출처:[islove8587님의 블로그] https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=islove8587&logNo=220608680181

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

출처:  [금백조의 개발 블로그:티스토리]https://goldswan.tistory.com/10

[MSSQL]SELECT INTO문을 통한 임시테이블 생성(SELECT INTO절)

 

 

#는 세션내에서, ## 전역에서 사용가능 

 

 

1. 임시 테이블

(1) 설명

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

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

   하지만 명시적으로 DROP해주길 추천한다.

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

 

[예제 1]

--===================================
--방법1 임시 테이블 생성하고 insert하기
--===================================
CREATE TABLE #TEMP_SCORE_TABLE(
	 CLASS NVARCHAR(06)
	,SCORE INT
)


INSERT INTO #TEMP_SCORE_TABLE
SELECT N'알고리즘' AS CLASS, 100 AS SCORE
UNION ALL
SELECT N'자료구조' AS CLASS, 99 AS SCORE
UNION ALL
SELECT N'JAVA' AS CLASS, 97 AS SCORE
UNION ALL
SELECT N'C#' AS CLASS, 96 AS SCORE

--===================================
--방법2 SELECT INTO문으로 #TEMP1 임시테이블 생성 
--===================================
SELECT *
INTO #TEMP1
FROM
(
	SELECT CLASS, SCORE FROM #TEMP_SCORE_TABLE
)A

--INSERT 실패
INSERT INTO #TEMP1
SELECT
'JavaScript'--10자리 문자열이므로 INSERT 실패
,94

--INSERT 성공
--서브 쿼리의 #TEMP_SCORE_TABLE 임시테이블의 CLASS가 NVARCHAR(06) 이므로
--#TEMP1에도 CLASS가 NVARCHAR(06)으로 복사되어 6자리까지 INSERT 가능
INSERT INTO #TEMP1
SELECT
'데이터베이스'--6자리 문자열이므로 INSERT 성공
,95

SELECT CLASS, SCORE FROM #TEMP1--데이터 확인

 

[예제 2]

SELECT * 
INTO #TEMP1
FROM
(
	SELECT N'알고리즘' AS CLASS, 100 AS SCORE
	UNION ALL
	SELECT N'자료구조' AS CLASS, 99 AS SCORE
	UNION ALL
	SELECT N'JAVA' AS CLASS, 97 AS SCORE
	UNION ALL
	SELECT N'C#' AS CLASS, 96 AS SCORE
)A

--INSERT 실패
--위의 SELECT문에서 #TEMP1생성시 가장 MAX인 문자열이 4자리(알고리즘, 자료구조) 였으므로
--4자리이상 문자열인 데이터베이스, JavaScript INSERT 실패
INSERT INTO #TEMP1
SELECT
'데이터베이스'--6자리 문자열이므로 INSERT 실패
,95
--INSERT 실패
INSERT INTO #TEMP1
SELECT
'JavaScript'--10자리 문자열이므로 INSERT 실패
,94

--INSERT 성공
INSERT INTO #TEMP1
SELECT
N'CSS'--3자리 문자열이므로 INSERT 성공
,93


SELECT CLASS, SCORE FROM #TEMP1--데이터 확인

 

[SELECT INTO문 행 삽입시 ORDER BY를 써도 정렬된 상태로 행 삽입이 안 되는 것을 확일할 수 있는 예제]

--INTO문 행 삽입시 ORDER BY 정렬 가능 여부 확인 예제
SELECT SEQ_NUM, [NAME]
INTO #TEMP_SEQ
FROM
(
	SELECT 1 AS SEQ_NUM, '순서1' AS [NAME]
	UNION ALL
	SELECT 3,  '순서3'
	UNION ALL
	SELECT 5,  '순서5'
	UNION ALL
	SELECT 2, '순서2'
	UNION ALL
	SELECT 4, '순서4'
)A
ORDER BY A.SEQ_NUM

--실제 조회시 정렬되지 않은 행들이 조회됨.
SELECT * FROM #TEMP_SEQ

 

(2) 형태

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

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

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

 

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

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

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

 

(3) 장점

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

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

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

④ ALTER TABLE이 가능하다.

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

 

2. 테이블 변수

(1) 설명

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

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

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

 

[예제]

CREATE PROC dbo.usp_TableVar
AS
 
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) 장점

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

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

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

 

(3) 제약사항

① FK를 사용할 수 없다.

② ALTER TABLE를 할 수 없다.

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

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

 

3. 임시 테이블, 테이블 변수

(1) 필요한 경우

① 여러 단계를 거쳐 동일한 데이터에 반복작업을 해결할 때

② 복잡한 쿼리를 단순화 시킬때

③ 서버상의 커서(Cursor) 사용으로 인한 부하를 줄이고자 할 때

 

(2) 차이점

 

	임시 테이블(#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 컬럼을 지정해서 데이터가 정렬된 형태로 저장하여 사용