신문과 프로그래머.

콤마로 된 문자열을 테이블로 변환 함수

[ DelimitedSplit8K ]

1. 테이블 반환 함수

2. ItemNumber : 순번, Item : 각 콤마구분값

3. InputParameter : @pString - 문자열, @pDelimiter - 구분값(char(1))

<사용예>

1. 문자열 관련

SELECT * FROM DelimitedSplit8K('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')  

2. 테이블과 조인

  - CROSS APPLY를 위해 SQL2005 이상 가능 ( 호환성모드 90 이상 )

[ fn_Split ]

1. 테이블 반환 함수

2. 각 콤마구분값

3. InputParameter : @Sql - 문자열, @Splits - 구분값

<사용예>

CREATE FUNCTION dbo.DelimitedSplit8K /*************************************************************************************************** Purpose: Split a given string at a given delimiter and return a list of the split elements (items). Usage Example: SELECT * FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter) Returns: iTVF containing the following: ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST) Item = Element value as a VARCHAR(8000) Notes: 1. Optimized for VARCHAR(8000) or less. 2. Optimized for single character delimiter. 3. Optimized for use with CROSS APPLY. 4. Does not "trim" elements just in case leading or trailing blanks are intended. 5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and compactness. 6. If you don't know how a Tally table can be used to replace loops, please see the following... http://www.sqlservercentral.com/articles/T-SQL/62867/ Revision History: Rev 00 - 20 Jan 2010 - Jeff Moden - Base 10 redaction for CTE. Rev 01 - 08 Mar 2010 - Jeff Moden - Changed UNION ALL to UNPIVOT for bit of extra speed. Rev 02 - 13 Mar 2010 - Jeff Moden - Removed one additional concatenation and one subtraction from the SUBSTRING in the SELECT List. ***************************************************************************************************/ --===== Define I/O parameters ( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE AS RETURN WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000). E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--10 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4) --===== Do the split SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber, SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item FROM cteTally WHERE N < LEN(@pString) + 2 AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter ; SELECT * FROM DelimitedSplit8K('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',') SELECT t.아이디,t.콤마로된필드,b.itemNumber,b.item FROM 대상테이블 t CROSS APPLY dbo.DelimitedSplit8K(t.m_interest,',') B WHERE isnull(t.콤마로된필드,'') <> '' ORDER BY T.아이디 CREATE FUNCTION [dbo]. [fn_Split] ( @Sql VARCHAR (8000), @Splits VARCHAR (10)) RETURNS @temp TABLE (item VARCHAR (100)) AS BEGIN Declare @i Int SET @Sql = RTrim (LTrim (@Sql)) SET @i = CharIndex (@Splits, @Sql) WHILE @i > =1 Begin INSERT @temp VALUES(Left (@Sql, @i-1)) SET @Sql = SUBSTRING (@Sql, @i + 1, LEN (@Sql ) - @i) SET @i = CHARINDEX (@Splits, @Sql) END ; IF @Sql < > '' INSERT @temp VALUES(@Sql) RETURN END SELECT * FROM fn_Split('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')

http://rocabilly.tistory.com/1

인덱스는 기본적으로 생성해두는것이 효율성에 좋습니다. 

데이터가 적을 경우는 기본적인 인덱스로 결과를 출력할 수 있도록 하고, 데이터가 많아질 수록 쿼리에 맞는 인덱스를 걸어주면 효율적입니다. 

인덱스는 일반적으로 한 테이블당 5개전후로 많이 잡습니다. 그 이상을 걸면 데이터의 양에 오히려 좋지 않은 영향을 줄 수 있기 때문입니다. 

인덱스는 기본적으로 전체 테이블의 10%정도를 가져오는데 효과적입니다. 

하지만 1000만건중에 100만건을 가져오는데는 인덱스가 효과적이라고 보기는 힘들지만 기본적은 인덱스는 필수입니다. 

현재 느리다고 생각되는 쿼리가 있다면 다음의 과정으로 인덱스를 만들어보세요. 

1. 쿼리 분석

현재 이 서버에 돌아가고 있는 쿼리를 알기 위해 다음을 실행합니다. 

select * from master..syscacheobjects

sql이란 부분에 현재 캐시되어있는 쿼리가 보입니다. 

이 쿼리들을 돌려가며 느리다고 느껴지는 쿼리를 찾습니다.

2. 기본 인덱스, 부하가 걸리는 인덱스를 체크

sp_helpindex A

이 명령으로 A테이블의 인덱스를 찾을 수 있습니다. 

3. 인덱스 생성

create index A_ix01 on A(aa)

처음엔 고유증가 번호등의 잘 변하지 않는 필드를 Clustered Primary Key로 잡아야 합니다. 최종적으로 데이터를 찾아가는 길을 만들기 위함입니다. 이게 없으면 전체를 메모리에 띄워야 하기 때문에 많은 리소스를 잡아먹습니다.

Join문과 Where에서 찾는 필드명은 index로 걸어주는 것이 좋습니다. text등의 900바이트가 넘는 데이터는 index가 생성이 되지 않으니 주의 하세요. 

index를 걸어줄 때 필드를 여러개를 한번에 지정하는 방법이 있고 한개씩 여러번 지정하는 방법이 있습니다.

한번에 여러개를 지정하는 방법의 예:

create index A_ix01 on A(aa, bb)

한개씩 여러번 지정하는 방법의 예:

create index A_ix01 on A(aa)

create index A_ix02 on A(bb)

위의 방법은 aa순으로 정렬된 뒤에 bb순으로 정렬하는 것입니다. 때문에 where절등에서 bb를 먼저찾거나 bb만 찾게 되는 경우 위의 인덱스는 타지 않습니다. 

대신 아래처럼 한개씩 여러번 지정하는 것에 비해 aa,bb순으로 찾는 경우 가장 빠른 효율을 줍니다. 따라서 위방법과 아래방법은 전혀 다른 인덱스가 됩니다. 참고하면서 만드세요. 

4. 테스트

인덱스를 만들어가면서 대량의 데이터의 쿼리를 돌려봅니다. 

인덱스를 바꿔가면서 가장 맞는 인덱스를 생성합니다.

최초로 돌릴 경우는 메모리에 올리는 시간 때문에 느릴 수 있습니다. 때문에 쿼리를 여러번 돌린후 마지막 시간을 재는게 좋습니다.

5. 불필요한 인덱스 삭제

drop index AA.AA_ix01 

반드시 테이블명.인덱스명 의 형식으로 지워야 지워집니다. 

* syscacheobjects에 비슷한 쿼리가 많다면 그 쿼리는 Static SQL을 이용하는 것이 좋습니다. Static SQL을 이용하면 같은 쿼리의 변수만 바뀌는 식의 쿼리를 하나의 쿼리로 인식하여 처리하므로 비슷한 캐시를 줄여 캐시를 활용할 공간이 넓어집니다. 

Code Formatter. 코드 정렬 도구.

SQL Server Management Studio 쿼리를 작성하다 보면, 토드나 SQL Gate 등 코드정렬(포맷) 기능이 너무 아쉽다. 

이를 Cover 하는 기능이 아래와 같이 제공된다.

파일명 : SqlFormatterSSMSAddIn.Setup.1.4.4.msi.zip

관련 링크 : http://architectshack.com/PoorMansTSqlFormatter.ashx

관련 링크 : http://www.sqler.com/504675

>> SP

-- 변경

GRANT ALTER ON SP이름 TO DB이름

GO

-- 소유권 가져오기

GRANT TAKE OWNERSHIP ON SP이름 TO DB이름

GO

-- 실행

GRANT EXECUTE ON SP이름 TO DB이름

GO

-- 정의 보기

GRANT VIEW DEFINITION ON SP이름 TO DB이름

GO

-- 제어

GRANT CONTROL ON SP이름 TO DB이름

GO

>> 테이블

-- 변경 내용 추적 보기

GRANT VIEW CHANGE TRACKING ON 테이블이름 TO DB이름

GO

-- 변경

GRANT ALTER ON 테이블이름 TO DB이름

GO

-- 삭제

GRANT DELETE ON 테이블이름 TO DB이름

GO

-- 삽입

GRANT INSERT ON 테이블이름 TO DB이름

GO

-- 선택

GRANT SELECT ON 테이블이름 TO DB이름

GO

-- 소유권 가져오기

GRANT TAKE OWNERSHIP ON 테이블이름 TO DB이름

GO

-- 업데이트

GRANT UPDATE ON 테이블이름 TO DB이름

GO

-- 정의 보기

GRANT VIEW DEFINITION ON 테이블이름 TO DB이름

GO

-- 제어

GRANT CONTROL ON 테이블이름 TO DB이름

GO

-- 참조

GRANT REFERENCES ON 테이블이름 TO DB이름

GO

DECLARE @dateReg datetime

SET @dateReg = GetDate()

SELECT Convert(nvarchar(20), @dateReg, 100) FROM [work] WHERE [idx] = 483

-> 07 15 2005 12:00AM

SELECT Convert(nvarchar(20), @dateReg, 101) FROM [work] WHERE [idx] = 483

-> 07/15/2005

SELECT Convert(nvarchar(20), @dateReg, 102) FROM [work] WHERE [idx] = 483

-> 2005.07.15

SELECT Convert(nvarchar(20), @dateReg, 103) FROM [work] WHERE [idx] = 483

-> 15/07/2005

SELECT Convert(nvarchar(20), @dateReg, 104) FROM [work] WHERE [idx] = 483

-> 15.07.2005

SELECT Convert(nvarchar(20), @dateReg, 105) FROM [work] WHERE [idx] = 483

-> 15-07-2005

SELECT Convert(nvarchar(20), @dateReg, 106) FROM [work] WHERE [idx] = 483

-> 15 07 2005

SELECT Convert(nvarchar(20), @dateReg, 107) FROM [work] WHERE [idx] = 483

-> 07 15, 2005

SELECT Convert(nvarchar(20), @dateReg, 108) FROM [work] WHERE [idx] = 483

-> 12:23:59

SELECT Convert(nvarchar(20), @dateReg, 109) FROM [work] WHERE [idx] = 483

-> 07 15 2005 12:23:59:

SELECT Convert(nvarchar(20), @dateReg, 110) FROM [work] WHERE [idx] = 483

-> 07-15-2005

SELECT Convert(nvarchar(20), @dateReg, 111) FROM [work] WHERE [idx] = 483

-> 2005/07/15

SELECT Convert(nvarchar(20), @dateReg, 112) FROM [work] WHERE [idx] = 483

-> 20050715

SELECT Convert(nvarchar(20), @dateReg, 113) FROM [work] WHERE [idx] = 483

-> 19 07 2005 12:23:36:

SELECT Convert(nvarchar(20), @dateReg, 114) FROM [work] WHERE [idx] = 483

-> 12:23:59:793 

STUFF 와 FOR XML PATH  테스트 !

테스트 시작 - TABLE2의 데이터를 한 ROW로 나타냄 : 결과

SELECT  T3.T1_ID,  STUFF( ( SELECT ',' + CAST(T2.T2_ID AS CHAR) FROM #TABLE1 AS T1          INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   WHERE T1.T1_ID = T3.T1_ID GROUP BY T2.T2_ID, T2.T1_ID ORDER BY T2.T1_ID FOR XML PATH('')  )  , 1, 1, '') AS T2_ID  FROM #TABLE1 AS T3  WHERE T3.T1_ID IN (1,2)  GROUP BY T3.T1_ID

STUFF 제거 : 앞에 , 살아남

SELECT T3.T1_ID,  --STUFF(  ( SELECT ',' + CAST(T2.T2_ID AS CHAR) FROM #TABLE1 AS T1          INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   WHERE T1.T1_ID = T3.T1_ID GROUP BY T2.T2_ID, T2.T1_ID ORDER BY T2.T1_ID FOR XML PATH('') ) AS T2_ID   --  , 1, 1, '') AS T2_ID  FROM #TABLE1 AS T3  WHERE T3.T1_ID IN (1,2)  GROUP BY T3.T1_ID

안의 SELECT 쿼리 테스트 결과 

SELECT ',' + CAST(T2.T2_ID AS VARCHAR) AS T2_ID  FROM #TABLE1 AS T1  INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID   --WHERE T1.T1_ID = T3.T1_ID  GROUP BY T2.T2_ID, T2.T1_ID  ORDER BY T2.T1_ID

- STUFF

STUFF(문자열, 시작위치, 변경할 문자길이, 변경할 문자)

EX) STUFF('ABCDEF', 2, 2 , 'abc') => 'AabcDEF'
두번째부터 시작하여, 2 길이의 문자를 abc로 변경

기본적으로 SQL 에서 제공해주는 데이터로 우리는 원하는 기간동안의 날짜를 모두 출력 해 낼 수 있다. 

select convert(char(10),dateadd(증가시킬데이터(m:월, d:일, hh:시간, mm:분) ,number,'시작날짜'),120) as regdate
from master..spt_values with(nolock) 
where type = 'P' 
and convert(char(10),dateadd(d,number,'시작날짜'),120) < '마지막날짜'

이렇게 원하는 기간 내의 날짜를 출력 해 낼 수 있다.

ex)

select convert(char(10),dateadd(d,number,'2012-04-24'),120) as regdate
   from master..spt_values with(nolock) 
   where type = 'P' 
   and convert(char(10),dateadd(d,number,'2012-04-24'),120) < DateAdd("m", 1, '2012-04-24')  

 마지막 날짜를 mssql 에서 제공하는 DateAdd 함수를 이용해서 한달을 더해 출력 해 보았다

결과는 다음과 같다.

2012-04-24
2012-04-25
2012-04-26
2012-04-27
2012-04-28
2012-04-29
2012-04-30
2012-05-01
2012-05-02
2012-05-03
2012-05-04
2012-05-05
2012-05-06
2012-05-07
2012-05-08
2012-05-09
2012-05-10
2012-05-11
2012-05-12
2012-05-13
2012-05-14
2012-05-15
2012-05-16
2012-05-17
2012-05-18
2012-05-19
2012-05-20
2012-05-21
2012-05-22
2012-05-23 

이걸 이용하여 left조인 하여 원하는 날짜에 원하는 데이터를 추출 해 낼 수 있다.. 

보너스. 

활용을 조금 더 하자면 시간대 별 데이터 까지 출력이 가능하다. 

select right(convert(char(13),dateadd(hh,number,'시작날짜'),120),2) as regdate
from master..spt_values with(nolock) 
where type = 'P' 
and convert(char(10),dateadd(hh,number,'시작날짜'),120) < '마지막날짜' 

이렇게 해서 원하는 기간내에서 해당되는 날짜와 또한 그 날짜의 시간대 별 데이터까지  

group by 시킬 수가 있다. 

실전활용예제 )

select 
   regdate,
   isnull(tot,0),
   isnull(cnt,0)
from 
(
 select right(convert(char(13),dateadd(hh,1,'2012-04-24'),120),2) as regdate
 from master..spt_values with(nolock) 
 where type = 'P' 
 and convert(char(10),dateadd(hh,number,'2012-04-24'),120) < DateAdd(d, 1, '2012-04-24')
) as t1 
left join 
(
 SELECT right(CONVERT(char(13), d_regdate, 120),2) as 열1, sum(열1) as tot, count(열1) as cnt 
 FROM 데이터를 조회할 테이블
 WHERE convert(char(10),D_REGDATE,120) >= '2012-04-24'
 AND convert(char(10),D_REGDATE,120) <= '2012-04-24'
  group by right(CONVERT(char(13), d_regdate, 120),2)
) as t2
on t1.regdate = t2.d_regdate
group by t1.regdate, t2.d_regdate, tot,cnt
order by t1.regdate; 

난 하루치만 조회할 예정이었으므로 음하하핫. 

※이렇게 left join문을 써서 복잡하게 조회 한 까닭은 데이터를 페이지에 뿌려주는데, join을 하지 않고 그냥 쿼리문을 썼을 때 데이터가 없는 시간대에는 0을 해주고 싶은데, 데이터 자체가 없기에 그 시간대는 아예 결과에 나타나지 않았다.

예를 들면

01    1233      4

02    1234      2

04    3412      4

이런식으로 3시 대의 결과가 빠져서 된다.

이를 해결해 주기위해서 웹언어로 구현을 해도 되겠지만, 그렇게 되면 조회되는 데이터에 따라 달라지기에 소스가 복잡해 질 수 있기에, 모든 시간대를 결과로 출력하고 isnull(열이름,0) 을 통해

null일때는 0을 출력하게 만들어줌으로 결과가 없는 시간대에는

01 1233 4

02 1234 2

03    0  0

04 3412 4

이라는 결과를 얻을 수 있다. 

자세한 설명은 읽어보지 않았지만 써보면 master..spt_values 단순히 0, 1, 2, 3, 4,  쭉 증가하는 값을 가진 테이블이라고 보면 될 것 같다.

먼저 중첩 Transaction에 대해서 알아보도록 하겠습니다. 중첩 Transaction은 말 그대로 Transaction 안에 Transaction을 또 다시 정의하는 것을 말합니다.

예제의 그림을 보면서 설명을 드리도록 하겠습니다.

그림을 보시면 상당히 복잡한 문장 같은 데요. 하나하나 살펴보도록 하겠습니다.

이 문장은 기존에 임대되었던 임대내역을 갱신하는 문장입니다. 즉 고객이 테이프를 빌려가면서, 이미 임대에 대한 지불을 끝내고 나서, 테이프를 하나 더 빌리는 경우, 임대정보를 기존에 입력한 내역에 대해서 추가를 하는 업무입니다. 따라서 임대정보 Table에 있는 총계 정보를 갱신하고, 임대품목에는 하나의 임대내역을 추가하여야 합니다.

먼저 BEGIN TRAN TEST_1은 Transaction에 대해서 TEST_1이라는 명칭을 부여하였습니다. 이 Transaction은 모든 전체적인 문장의 Transaction입니다. 먼저 임대정보 Table에 있는 임대번호가 '2000010602'인 Data에 대해서 총계를 1500으로 갱신을 하는 문장입니다. 그리고 그 다음 문장을 보시면, BEGIN TRAN TEST_2로 또 하나의 BEGIN TRANSACTION 문장을 사용하고 있습니다. 이것이 바로 중첩 Transaction입니다. 자세한 설명은 잠시 후 그림을 통해서 드리도록 하겠습니다.

그 다음은 임대품목에 하나의 임대내역을 추가하는 문장과 해당 테이프의 대여여부를 갱신하는 문장입니다.

다음의 문장이 좀 중요한데요 보시는 바와 같이, Error에 대한 검사를 하여, 에러가 발생된 경우에는 ROLLBACK TRAN 문장을 수행하고, 그렇지 않은 경우, 즉 에러가 발생하지 않은 경우에는 두 개의 Transaction을 모두 COMMIT하여 Database에 적용을 하는 문장입니다. Transaction은 두 개이기 때문에 COMMIT문장은 두 번 쓰인 것은 이해할 수 있지만, ROLLBACK 구문이 한 번 쓰인 이유는 무엇일까요?

우선 중첩 Transaction에 대해서 설명을 드리고 ROLLBACK 구문이 한 번 사용된 이유에 대해서 설명을 드리도록 하겠습니다.

아래의 그림은 앞선 문장의 Transaction을 간략하게 정리한 것입니다.

보시는 바와 같이 BEGIN TRANSACTION 구문을 이용하여 Transaction을 시작을 한 경우에는 반드시 COMMIT을 이용하여 Transaction을 마무리 하여야 합니다.

또한 처음 시작한 Transaction 안에 여러 개의 Transaction을 중복하여 사용을 할 수 있습니다. 즉 Transaction 안에 또 다른 Transaction을 만들 수 있다는 것입니다.

앞선 예제에서는 Transaction 마다 이름을 주었지만, 이들은 생략이 가능합니다.

만약 이들 이름을 생략하게 된다면 COMMIT TRAN TEST_2는 바로 앞서서 BEGIN TRANSACTION TEST_2를 이용하여 시작하였던 Transaction에 대해서 COMMIT를 실행합니다. 즉 COMMIT 구문에서 명칭을 부여하지 않은 경우에는 가장 가까이 위치한 Transaction에 대해서 COMMIT 구문을 적용하게 된다는 것입니다.

그럼 ROLLBACK 구문은 어떨까요?

물론 Transaction이 하나의 BEGIN TRANSACTION의 문장으로 시작한 경우, ROLLBACK구문은 해당 Transaction의 모든 연산을 취소하게 됩니다. 하지만 하나 이상의 Transaction. 즉 방금 전 살펴보았던, 중복 Transaction일 경우 어떻게 될까요?

ROLLBACK의 경우에는 중복된 Transaction의 개수와는 상관없이, 가장 먼저 시작한 Transaction에 대해서 Rollback을 수행하게 됩니다. 즉 해당 Transaction 안에서 몇 개의 Transaction이 있는지에 상관없이 전체 Transaction에 대한 실행취소를 하게 됩니다.

즉 BEGIN TRANSACTION에 의해서 시작된 수 만큼 COMMIT 문장을 실행하는 것과는 조금 다른데요. 앞선 예제에서 ROLLBACK 구문이 한 번 사용된 이유는 여기에 있습니다. 따라서 앞서 예제 문장에서 만약 에러가 발생되었다면, 모든 문장에 대한 연산을 취소하고 맨 처음 시작한 TEST_1 Transaction이 시작되기 전의 상태로 되돌아가게 됩니다.

2. SAVEPOINT

조금은 불편하죠? 예제는 몇 개 되지않은 연산이었지만, 만약 여러 연산을 한 번에 실행하여야 하는 경우에, 마지막 연산에서 에러가 발생하여 Rollback으로 Transaction을 취소하였다면, 서버자원의 상당한 낭비도 초래하게 됩니다. 앞선 예제에서 만약 TEST_2 Transaction에 대한 내역만 취소하고 나머지 연산은 성공할 수 있도록 한다면 이러한 낭비는 줄일 수 있을 것입니다.

이렇게 특정 시점까지만 ROLLBACK을 할 수 있도록 지원하는 것이 바로 SAVEPOINT입니다.

우선 SAVEPOINT의 기본적인 구문을 살펴보도록 하겠습니다.

보시는 바와 같이 COMMIT 구문이나 ROLLBACK 구문과 사용방법은 같습니다.

예제를 통해서 설명을 드리도록 하겠습니다.

그림을 보시면 우편번호 Table에 Data를 입력하는 3개의 입력연산이 있습니다.

보시는 바와 같이 두 번째 Line을 보시면, SAVE TRAN 구문 다음에 SAVEPOINT의 명칭을 명시하여 SAVEPOINT를 정의하고 있습니다.

이렇게 SAVEPOINT를 정의하고, 다시 ROLLBACK을 하기 위해서는 ROLLBACK TRANSACTION 문 다음에 SAVEPOINT로 지정된 명칭을 명시하여 주면 됩니다.

자세한 내용은 실습을 통해서 설명을 드리도록 하겠습니다.

3. Transaction의 처리순서

이렇게 SQL Server는 Transaction을 이용하여 Data의 일관성을 유지할 수 있도록 지원하고 있습니다. 그럼 SQL Server가 어떻게 내부적으로 Transaction을 처리하는지에 대해서 알아보도록 하겠습니다.

그림에서 보시는 것과 같은 문장을 실행하였을 경우, MSSQL Server는 어떠한 처리과정을 거치는지 알아보도록 하겠습니다. 앞선 강좌에서 미루었던 Log에 대한 부분도 여기서 설명을 드리도록 하겠습니다.

설명을 드리기 전에 우선, Cache에 대한 부분에 대해서 알아보도록 하겠습니다. 앞서서 MSSQL Server는 Data에 대한 부분은 Data 파일에, Log에 대한 부분은 Log 파일에 저장을 한다고 하였습니다.

이렇게 Data는 SQL Server가 설치된 Computer의 Hard Disk 상에 존재하게 됩니다. 그렇다면 사용자에 의해서 Data에 대한 반환요구가 있을 경우, 매번 Hard Disk에서 해당 Data를 읽어와서 반환을 하는 작업을 거쳐야 하는데요. 이는 사용자가 느끼는 SQL Server의 속도와도 관계가 있기 때문에 성능상의 문제로 발전하게 됩니다.

이미 알고 계시듯이 Hard Disk에서 Data를 읽는 속도는 RAM의 속도와는 비교가 안될 정도로 차이가 나게 됩니다. MSSQL Server는 이렇게 성능향상을 위해서 Computer에 있는 RAM에 일부 Data를 저장하여 놓게 되는데요. 이를 Cache라고 합니다. Data와 Log에 대한 부분이 Hard Disk 상에 다른 파일로 존재하듯이, Cache에도 Data와 Log를 따로 저장하도록 하고 있습니다. 이를 Data Cache와 Log Cache로 나누어서 RAM을 사용하고 있습니다.

다시 앞선 예제문장으로 돌아가도록 하겠습니다.

1) 먼저 Transaction을 시작하는 BEGIN TRAN 문장이 수행되면, MSSQL Server는 Log Cache에 Transaction이 시작되었음을 알리는 내용을 기록합니다.

2) 두 번째로 UPDATE문이 실행이 되면, MSSQL Server는 갱신할 Data가 Data Cache에 있는지를 보고서, 만약 있다면 이를 사용하게 되고, 그렇지 않은 경우에는 Hard Disk로부터 해당 Data를 읽어와 해당 Data의 복사본을 Data Cache에 기록을 하고, 갱신문장을 수행하여 Data를 갱신하게 됩니다. 이때 Data에 대한 갱신하는 작업은 Log Cache에 기록되어 집니다. 이때 Dirty Page라는 것이 발생하게 되는데요. 이는 Cache 상의 복사본 Data와 Hard Disk 상에 있는 Data의 내용이 틀린 것을 말합니다. 앞서서 UPDATE 문장에서 기존에 있던 주소 Data(즉, Hard Disk에 저장되어 있는 Data)와 UPDATE 문장에 의해서 변경된 복사본 Data(즉, Cache에 저장되어 있는 Data)의 내용이 다른 Page를 말하는 것입니다.

3) 마지막으로 COMMIT TRANSACTION 문에 의해서 Transaction이 성공적으로 마치게 되면, Dirty Page의 내용은 Hard Disk 상의 Log 파일에 기록이 됩니다.

이렇게 SQL Server는 복사본의 내용을 Data 파일에 기록하기 전에, Log Cache를 Hard Disk의 Log 파일에 기록을 하게 됩니다. 이를 Database에서는 먼저 쓰기 로그(Write Ahead Log)라고 부릅니다.

Log만 기록하면 Data Cache에 기록된 Data는 언제 Hard Disk로 기록을 할까요? 다른 사용자가 해당 Data를 Hard Disk에서 읽어가면 갱신된 Data가 아니라 갱신되기 이전의 Data를 읽어가게 되는데, 그럼 Data의 일관성을 해치는 결과를 낳게 되는데. 이 문제들은 어떻게 할까요?

우선 첫번째로 Data Cache에 있는 변경된 Data는 Checkpoint나 Lazy Writer에 의해서 Hard Disk로 변경된 Data를 옮기게 됩니다. 바로 뒤에서 설명을 드리겠습니다.

두 번째, 다른 사용자가 Hard Disk 상에 있는 갱신되기 전의 Data를 읽어가면 어떻게 할까요? 이 문제는 걱정을 하지 않으셔도 됩니다. 앞서서 Data Cache에 대한 부분을 말씀을 드렸는데요. 만약 해당 Data에 대해서 다른 사용자가 사용을 하려 한다면, MSSQL Server는 Data Cache에 해당 Data가 있는지를 찾아보고 있다면, 그 Data를 사용하기 때문에, 다른 사용자가 갱신되지 않은 Data를 Hard Disk로부터 읽어가는 불상사는 일어나지 않습니다.

자, 그럼 방금 전 말씀을 드렸던 Checkpoint와 Lazy Writer는 무엇일까요? 하나하나 설명을 드리도록 하겠습니다.

- Checkpoint

우선 Checkpoint는 말 그대로 '검사점'입니다. 이 검사점 명령이 발생되면, SQL Server는 Cache에 있는 Data와 Log를 Hard Disk에 기록하게 됩니다. 즉, Hard Disk에 있는 내용과 사용자의 변경에 의해서 달라진 내용. 즉 Dirty Page의 내용을 Hard Disk로 넘겨주는 것입니다. 이는 언제 발생되는지는 알 수 없으며, 다만 Transaction의 사용정도와 Memory의 cache의 크기에 따라서 비주기적으로 발생됩니다.

- Lazy Writer

Computer 상의 RAM은 그 크기가 한정되어 있기 때문에, Cache 역시 그 크기가 한정되어 있습니다. 만약 Cache가 꽉 찬다면 어떻게 될까요? 이러한 경우에도 걱정하실 필요가 없습니다. Lazy Writer라는 것이 바로 Cache에 있는 내용을 바로 Hard Disk로 옮겨 적어주기 때문입니다.

이렇게 Cache에 있는 내용을 물리적인 Disk로 적어주는 기능은 Checkpoint와 Lazy Writer에 의해서 이루어지게 됩니다.

앞서서는 BEGIN TRANSACTION, COMMIT, ROLLBACK 등의 구문을 이용하여 Data의 일관성을 유지하는 것에 대해서 알아보았습니다. 이 외에도 MSSQL Server는 정전 또는 기타 다른 문제에 의해서 오류가 발생했을 경우에도 Data의 일관성을 유지할 수 있도록 지원을 하고 있습니다.

4. Transaction의 Recovery(복구)

Database에서 System 실패가 발생하는 경우는 여러 가지가 있습니다. 우선 Transaction 안에서 Overflow가 발생된다든지 하는 지역적인(Local) 고장과 정전이나 CPU의 고장, Hard Disk의 고장 등의 전역적인(Global) 고장 등이 있습니다. 지역적인 고장의 경우에는 해당 Transaction에만 영향을 미치지만, 전역적인 고장의 경우에는 고장 시 진행 중이던 Transaction에 치명적인 영향을 미치게 됩니다. 이런 경우 Database의 Data의 일관성이 깨어지게 되기 때문에, 이전의 일관된 상태로 되돌리기 위한 복구(Recovery) 작업이 필요로 하게 됩니다.

이러한 시스템 복구(Recovery)작업에 대해서 고장유형별로 알아보도록 하겠습니다. 먼저 시스템 고장일 경우의 시스템 복구에 대해서 알아보도록 하겠습니다.

▣ System 고장

시스템 고장은 예를 들어 정전과 같은 경우가 발생되는 경우, 현재 진행 중인 Transaction에 치명적인 영향을 미치게 됩니다. 이러한 경우에는 운영체제가 다시 시작될 때, MSSQL Server가 자동적으로 Transaction에 대한 내용을 다시 시작하거나(redo, roll forward), Transaction에 대한 내용을 취소하든지(undo, rollback) 하도록 지원을 합니다. 이러한 경우 시스템이 실패하였기 때문에, Cache에 기록된 내용은 이미 알 수가 없으므로, Log 파일의 기록에 의존할 수밖에 없습니다. 따라서 Log 파일에 기록된 내용을 읽어서 Database의 내용을 바꾸게 됩니다.

그럼 어떻게 Transaction의 내용을 복구하는지에 대해서 알아보도록 하겠습니다.

우선 그림을 보시면 5개의 Transaction이 시간별로 실행되고 종료되는 것을 표로 작성을 하였습니다. 보시는 바와 같이 일부는 Checkpoint가 발생되기 전에 이미 Commit이 되었고, 또 다른 Transaction은 Commit이 되기 전에 정전으로 인해서 Transaction에 대한 정보를 잃어버리게 되었습니다. 이들 각각의 Recovery(복구)작업은 다른 형태로 진행되게 됩니다.

자세한 내용은 실습을 통해서 설명을 드리도록 하겠습니다.

▣ 장치고장

다음은 장치고장의 경우입니다. 장치고장은 예를 들어 Hard Disk의 고장 또는 Disk Controller와 같은 Hardware의 고장 등이 있을 수 있습니다. 이러한 경우는 시스템 고장보다 더욱 심각한 경우인데요. 이 경우에는 앞서 시스템 고장과 같이 SQL Server가 자체적으로 복구를 할 수 없는 경우입니다. 따라서 관리자에 의해서 Data에 대한 복구가 이루어져야만 합니다. 장치고장에 대한 복구는 Database의 Backup과 Restore와 관련된 부분에서 자세하게 설명을 드리도록 하겠습니다.

5. Consideration for Using Transactions

Data의 일관성을 위해서 Transaction을 사용하는 것은 좋은 습관입니다. 하지만 무분별하게 Transaction을 오랫동안 사용하고 있다든지, 중첩 Transaction을 남용하는 것은 상당히 위험합니다. 이렇게 Transaction을 사용하는데 있어서 몇 가지 주의해야 할 점이 있습니다.

▣ Transaction Guidelines

우선 Transaction에 대한 주의할 점에 대해서 알아보도록 하겠습니다.

• 가능하면 Transaction은 짧게 사용하여야만 합니다. 특히 WHILE문과 같이 Loop를 수행하는 문장이나 Data Definition Language와 같은 구문은 짧게 사용할수록 좋습니다. 이는 Lock과도 연관이 되는데요. Lock에 대해서는 뒤에서 설명을 드리도록 하겠습니다.
• Transaction을 시작하기 전에, 필요한 사용자의 정보는 미리 받아 놓는 것이 좋습니다. 만약 Transaction이 정의된 상태에서 사용자의 정보를 얻거나 사용자에 의해서 입력을 받아야 하는 경우에는 Transaction이 수행되고 있는 동안에, 사용자가 해당 Data를 입력하지 않고 있다면, Transaction은 종료되지 않고 사용자의 입력을 계속해서 기다리게 됩니다.
• Transaction 내에서 INSERT, UPDATE, DELETE 구문이 주가 되어야만 합니다. 또한 이들 구문에 의해서 영향을 받는 Row는 적어야만 합니다.

▣ Issues in Nesting Transactions

다음은 중첩 Transaction에 대한 내용입니다.

중첩 Transaction의 사용은 제한이 없지만, 가능하면 사용하지 않는 것이 좋습니다. 앞서 중첩 Transaction에서 설명을 드린 것과 같이 중첩된 Transaction과는 상관없이 가장 먼저 정의된 Transaction이 Commit 되었는지, 아니면 Rollback 되었는지에 따라서 Transaction의 성공여부가 결정되기 때문에 중첩 Transaction은 의미를 가지지 못합니다.

@@trancount라는 전역변수를 사용하여 현재 정의되어 있는 Transaction의 수를 반환 받을 수 있습니다. 만약 @@trancount가 0인 경우에는 어떠한 Transaction도 정의되어 있지 않다는 것을 뜻합니다. 또한 DBCC OPENTRAN 구문을 이용하여도 Transaction에 대한 정보를 얻을 수 있습니다.

6. Setting the Implicit Transactions Option

앞서서 묵시적 Transaction에 대해서 설명을 드렸습니다. 묵시적 Transaction 역시 나름대로 장점을 가지고 있지만, 항상 BEGIN TRAN으로 Transaction을 시작하고, COMMIT이나 ROLLBACK 구문을 이용하여 Transaction을 종료하는 것이 잘못된 입력을 막아서 Data의 일관성을 지킬 수 있도록 할 수 있기 때문입니다.

묵시적 Transaction Option은 이렇게 Transaction을 정의하는 것을 좀 더 쉽게 할 수 있도록 지원을 하고 있습니다. 묵시적 Transaction을 설정하는 기본적인 문장은 다음과 같습니다.

만약 IMPLICIT_TRANSACTION의 Option을 ON으로 하였다면, 특정 문장이 시작되면, 그 전에 BEGIN TRAN이라는 구문을 SQL Server가 자동으로 실행하여 줍니다. BEGIN TRAN 구문을 사용자가 직접 정의하지는 않았지만, SQL Server에서 정의를 해주었기 때문에 이 문장은 반드시 COMMIT 또는 ROLLBACK 구문을 이용하여 종료하여야 합니다.이렇게 MSSQL Server가 자동으로 BEGIN TRAN 문장을 실행하여 주기 때문에, 중첩 Transaction에 대한 정의는 불가능합니다.

또한 다음의 문장이 실행되면, MSSQL Server는 암시적으로 BEGIN TRAN 구문을 실행하여 Transaction을 시작하게 됩니다.

묵시적 Transaction Option의 기본값은 OFF이기 때문에 이를 사용하기 위해서는 반드시 SET 구문을 이용하여 암시적 Transaction의 Option을 ON으로 변경하여 주어야 합니다.

자세한 사용 예는 실습을 통해서 설명을 드리도록 하겠습니다

여기서 주의하여야 할 점은 묵시적 Transaction으로 정의된 문장은 반드시 COMMIT나 ROOLBACK 구문을 이용하여 Transaction을 종료하여야만 합니다.

7. Restrictions on User-Defined Transactions

사용자 지정 Transaction에는 몇 가지 제약이 있습니다. 몇몇 System Stored Procedure는 사용자 지정 Transaction에서 사용될 수 없습니다. 그 이유는 임시 Table을 생성하기 때문입니다. 예를 들어 sp_dboption과 같은 System Stored Procedure는 사용될 수 없습니다.

또한 다음의 문장들 역시 명시적 Transaction에서는 사용될 수 없습니다.