김석주의 블로그

5. 새 데이터 타입

SQL Server 2008에는 다음과 같은 새로운 데이터 타입이 추가되었다.

DATE

오직 날짜 정보만을 담을 수 있는 DATE 타입이 추가되었다. DATETIME과 달리 날짜 정보만을 담기 때문에 데이터 크기가 3바이트이고, 0001-01-01 부터 9999-12-31까지의 날짜를 저장할 수 있다.

지금까지 디비 스키마를 정의하다 보면 생일이나 시작/끝 날짜처럼 날짜 정보만이 중요하고 시간 부분을 사용하지 않는다 할지라도, DATETIME을 사용할 수 밖에 없었다. 이제 DATE 타입의 지원으로 그러한 곳에 적절히 사용할 수 있게 되었다. 특히 DATE 타입은 8바이트의 DATETIME을 대신할 수 있어 저장 공간도 많이 절약할 수 있다.

TIME

TIME은 오직 시간 정보만 담기 위한 새로운 데이터 타입이다. TIME 데이터 타입은 100 nano seconds까지 데이터를 저장할 수 있으며, 데이터 크기는 3 – 5 바이트이다.

DATETIME2

DATETIME2는 앞에서 소개한 DATE와 TIME을 합해 놓은 새로운 데이터 타입이다. DATE 처럼 0001-01-01 부터 9999-12-13까지 저장할 수 있으며, TIME 처럼 100 nano seconds까지 저장할 수 있다. 데이터 크기는 6-8 바이트이다.

DATETIMEOFFSET

DATETIMEOFFSET은 앞에 나온 DATETIME2 타입에 timezone 정보가 추가된 새로운 데이터 타입이다.

6. Merge에도 Output 사용 가능

SQL Server 2005부터 사용 가능했던 키워드 중에 하나가 Output이다. 이 output는 각 SQL 명령에서 영향을 받은 데이터를 출력하고자 할 때 사용하는 것이었는데, 이 output이 새로운 명령어인 merge에서도 사용 가능하다.

그런데 Merge의 경우 Insert/update/delete 의 3가지 연산을 동시에 할 수 있기 때문에 output으로 영향받은 데이터가 어떻 연산으로 생긴 것인지 알 필요가 있다. 이를 위해 “$action”이라는 함수가 추가되었다. 이 $action 함수는 ‘INSERT’, ‘UPDATE’, ‘DELETE’ 중 하나를 리턴하게 된다. 다음은 output과 $actoin을 사용한 예이다.

if object_id(‘TargetTbl’) is not null drop table TargetTbl
create table TargetTbl (
    id int,
    name varchar(30)
)
insert into TargetTbl
values
    (1, ‘name1′),
    (2, ‘name2′)
if object_id(‘SourceTbl’) is not null drop table SourceTbl
create table SourceTbl (
    id int,
    name varchar(30)
)
insert into SourceTbl
values
    (2, ‘name2_2′),
    (3, ‘name3′)
merge TargetTbl As t
using SourceTbl As s on t.id=s.id
when matched then
    update set
        t.name = s.name
when not matched then
    insert (id, name)
    values (s.id, s.name)
output $action, inserted.*, deleted.*;

7. Grouping sets

SQL Server 2008의 T-SQL 변화 중에 가장 큰 중에 하나는 Grouping sets가 추가된 것이다. 지금까지는 오직 한가지의 grouping만 가능하였지만, 2008부터 한 가지 이상의 grouping이 가능해진 것이다. 가령 다음과 같은 샘플 테이블과 데이터가 있다고 하자. (이 예제는 Craig Freedman의 블로그에서 가져온 것이다.)

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

 

select * from Sales

EmpId       Yr          Sales

———– ———– ———————

1           2005        12000.00

1           2006        18000.00

1           2007        25000.00

2           2005        15000.00

2           2006        6000.00

3           2006        20000.00

3           2007        24000.00

이 테이블을 현재처럼 하나의 컬럼 셋트로 Grouping 하게 되면 다음과 같이 될 것이다.

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr

 

EmpId       Yr          Sales

———– ———– ———————

1           2005        12000.00

2           2005        15000.00

1           2006        18000.00

2           2006        6000.00

3           2006        20000.00

1           2007        25000.00

3           2007        24000.00

 

SELECT EmpId, SUM(Sales) AS Sales
FROM Sales
group by EmpId

 

EmpId       Sales

———– ———————

1           55000.00

2           21000.00

3           44000.00

SQL Server 2008에서는 이 2가지 Grouping을 셋트로 묶을 수 있게 되었다. 다음은 Grouping Set를 이용한 명령이다.

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))

 

EmpId       Yr          Sales

———– ———– ———————

1           2005        12000.00

1           2006        18000.00

1           2007        25000.00

1           NULL        55000.00

2           2005        15000.00

2           2006        6000.00

2           NULL        21000.00

3           2006        20000.00

3           2007        24000.00

3           NULL        44000.00

앞에서 정의한 2가지 grouping 결과를 하나로 묶어 놓은 결과가 나오는 것을 볼 수 있다.

8. Table 타입 매개변수

SQL Server에서 Stored Procedure를 정의할 때 한가지 부족했던 부분은 매개변수로 테이블을 사용할 수 없다는 것이었다. 그래서 일반적으로 테이블 이름을 varchar 타입으로 전달해서 Dynamic SQL을 사용한다거나, Temporary 테이블을 만드는 방식을 사용하곤 하였다. 그런데 SQL Server 2008부터는 테이블을 매개변수로 사용할 수 있게 되었다. 이를 위해 Table Type이란 형식을 정의하게 된다. 예를 들어 다음은 Book이라는 Table Type을 정의하는 명령이다.

Create Type Book as Table
(Title nvarchar(100) not null,
Author nvarchar(100) not null)

그리고 이렇게 정의된 Table Type을 Stored Procedure의 매개변수로 사용하면 된다. 다음은 Table Type 매개변수를 사용한 Stored procedure의 정의를 보여 준다.

create procedure PrintBook(
@bk Book READONLY)
as
begin

    select * from @bk;

end

이 stored procedure에서 Table Type 매개변수를 사용할 때 READONLY를 사용하고 있는 것을 볼 수 있는데, 이는 procedure 안에서 Table Type 변수의 값을 바꿀 수 없고 참조만 할 수 있다는 의미이다. 아직까지 SQL Server 2008에서는 READONLY만 가능하다고 한다. 아마 차기 버전에서는 업데이트도 가능하지 않을까 싶다.

이제 이 procedure를 사용하는 방법을 살펴보자. 이를 위해서 먼저 Table Type 변수를 선언하여야 하고 그 변수에 값을 입력한다. 그런 후, stored procedure를 호출하면 되는 것이다.

declare @b As Book;

insert into @b
values
(‘Book1′, ‘Author1′),
(‘Book2′, ‘Author2′)

 

exec PrintBook @b

SQL Server가 2005에서 2008로 업그레이드 된지도 벌써 몇달이 지났다. 이제 본격적으로 SQL-Server 2008로 옮겨가야 할 때가 온 것 같다. 그래서 이번 기회에 SQL Server 2008이 어떻게 업그레이드 되었는지 살펴보고자 한다. 그 중에서 프로그래머에게 필수인 T-SQL에 대해 집중적으로 살펴보려고 한다.

1. 변수 선언과 함께 초기화 가능

T-SQL에서 변수를 선언할 때 초기화가 가능해졌다. 사실 C#이나 자바와 같은 언어에서는 당연한 기능이지만, T-SQL에서는 그동안 지원이 되지 않았다. 그래서 다음과 같이 변수 선언과 초기화가 별도의 문장으로 만들어져야 했다.

declare @i int
set @i = 0

그렇지만 SQL Server 2008에서는 다음과 같이 변수 선언과 함께 초기화가 가능해졌다. 어떻게 보면 당연한 이 기능은 그동안 많은 SQL 프로그래머들이 기다려왔던 것이다. 그래서 SQL Server 2008 강연에 가보면 이 기능에 가장 많은 박수가 나오는 것을 볼 수 있었다. (커다란 웃음과 함께…)

declare @i int = 0

참고로 SQL Server 2008에 포함된 개발 도구인 Management Studio에 중요한 기능이 업그레이드되었는데, 그것은 Visual Studio에서 볼 수 있었던 intellisense 기능이다. 코드를 입력할 때마다 상황에 가장 급접한 힌트가 드롭 다운으로 나오는 것이다. 아래 그림은 @i 변수를 선언한 후, select를 입력했을 때 나오는 intellisense를 캡쳐한 것이다. 그동안 Visual Studio를 사용하면서 가장 부러웠던 기능을 SQL 프로그래밍에서도 사용할 수 있게 된 것이다.

2. 새로운 연산자

다음과 같은 새로운 연산자 역시 다른 언어에서는 당연했던 것인데, 이번에 T-SQL에도 추가되었다.

+=, –=, *=, /=, %=

다른 언어에 익숙한 사람이라면 이 복합 연산자를 손쉽게 이해할 수 있을 것이다. 다음은 복합 연산자를 사용한 예이다.

declare @i int = 10
set @i *= 2
select @i

3. Row Constructors

테이블에 데이터를 넣을 때 values를 사용하는데, 이 경우 오직 한 줄의 데이터만 입력이 가능했었다. 그런데 SQL Server 2008 부터는 “,”를 붙힌 후, 여러 줄을 동시에 입력할 수 있게 된다. 다음은 동시에 2줄을 입력하는 예이다.

create table #test
(idx int, data varchar(10))

insert into #test
(idx, data)
values
(1, ‘test1′),
(2, ‘test2′)

새로운 Row Contructors를 사용하면 inline table expression이라는게 가능하다. 테이블을 직접 정의하지 않고도, values를 이용하여 inline 테이블을 정의할 수도 있다.

select *
from
(values
    (1, ‘test1′),
    (2, ‘test2′)
) tbl (idx, data)

4. Merge 추가

Insert, update, delete 를 주어진 조건에 따라 한 번의 명령으로 처리할 수 있는 Merge라는 새로운 명령어가 추가되었다. 가령 두개의 테이블을 비교해서 데이터가 이미 존재하면 데이터를 업데이트하고, 존재하지 않으면 추가하는 경우에 이 Merge를 통해 손쉽게 처리할 수 있게 된 것이다. 다음은 Merge를 사용한 예이다.

– Target 테이블

create table TargetTbl (
    id int,
    name varchar(30)
)
insert into TargetTbl
values
    (1, ‘name1′),
    (2, ‘name2′)

 

– Source 테이블
create table SourceTbl (
    id int,
    name varchar(30)
)
insert into SourceTbl
values
    (2, ‘name2_2′),
    (3, ‘name3′)

merge TargetTbl As t
using SourceTbl As s on t.id=s.id  — TargetTbl과 SrouceTbl을 Join
when matched then– id가 일치하는 경우 name 필드를 업데이트
    update set
        t.name = s.name
when not matched then – id가 일치하지 않는 경우 데이터 입력
    insert (id, name)
    values (s.id, s.name);

select * from TargetTbl;

Microsoft Press의 “Introducing Microsoft SQL Server 2008” 이란 책의 전체 내용을 담은 PDF 파일을 무료로 다운로드 할 수 있다. 다음 링크에 가서 간단히 등록하면 PDF 파일로 SQL-Server 2008 책을 다운로드할 수 있을 것이다.

SQL-Server 2008 다운로드