SQL Server 2005 TSQL sungheehadconsulting co kr SQL

SQL Server 2005 TSQL 하성희 sungheeh@adconsulting. co. kr 에이디컨설팅 / 대표 컨설턴트 SQL Server MVP

새로운 데이터 타입 • • varchar(max) nvarchar(max) varbinary(max) xml 데이터 타입

varchar(max) • SQL Server 2000 CREATE TABLE test 1 ( c 1 int, c 2 varchar(5000), c 3 varchar(5000) ); CREATE TABLE test 2 ( c 1 int, c 2 varchar(5000), c 3 text ); 경고: 'test' 테이블이 만들어졌으나 최대 행 크기(10029)가 행 당 최대 바이트 수(8060)를 초과합니다. 결과 행 길이가 8060바이트 를 넘으면 이 테이블의 행에서 INSERT 또는 UPDATE가 실패합니 다. INSERT test 1 VALUES (1, replicate('a', 5000), replicate('b', 5000)) 서버: 메시지 511, 수준 16, 상태 1, 줄 1 허용된 최대값(8060)보다 큰 크기의 행(10017)을 만들 수 없습 니다.

varchar(max) • SQL Server 2005 CREATE TABLE test 1 ( c 1 int, c 2 varchar(5000), c 3 varchar(5000)); INSERT test 1 VALUES (1, replicate('a', 5000), replicate('b', 5000)); CREATE TABLE test 2 ( c 1 int, c 2 varchar(5000), c 3 varchar(max)); INSERT test 2 VALUES (1, replicate('a', 5000), replicate('b', 9000));

Ranking 함수 예: 데이터 분석 • 예: SELECT RANK() OVER(ORDER BY City) AS RANK, RANK() OVER(PARTITION BY City ORDER BY Last. Name) AS PART_RANK, DENSE_RANK() OVER(ORDER BY City) AS DENSE_RANK, ROW_NUMBER() OVER(ORDER BY City) AS ROW_NUM, NTILE(4) OVER(ORDER BY City) AS NTILE_4, Last. Name, First. Name, City FROM Employees ORDER BY City, Last. Name • OVER 절 – 파티션 방식 제공 – 결과 집합의 행들을 정렬

RANK PART_RANK DENSE_RANK ROW_NUMBER NTILE_4 Ranking 함수 예: 데이터 분석 Last. Name 1 2 2 6 7 7 9 1 1 2 3 4 1 1 2 2 2 2 3 4 4 5 1 2 5 4 3 6 8 7 9 1 1 2 2 1 3 4 Leverling Buchanan Dodsworth King Suyama Peacock Callahan Davolio Fuller First. Name Janet Steven Anne Robert Michael Margaret Laura Nancy Andrew City Kirkland London Redmond Seattle Tacoma

Ranking 함수 예: 페이징 • 예: 50에서 60까지의 주문 정보를 일자순으로 정렬 WITH Ordered. Orders AS (select Sales. Order. ID, Order. Date, Row_Number() OVER (order by Order. Date) AS RN from Sales. Order. Header) SELECT * from Ordered. Orders WHERE RN between 50 and 60

새로운 관계형 연산자 PIVOT Make Year Sales Honda 1990 2000 Honda 1990 1000 Acura 1990 500 Honda 1991 3000 Acura 1991 300 Acura 1991 600 Acura 1992 800 SELECT * FROM Car. Sales PIVOT(SUM(Sales) FOR Year IN ([1990], [1991]))t Make Honda 1990 3000 1991 3000 Acura 500 900

새로운 관계형 연산자 UNPIVOT Make Year Sales Acura Honda 1990 1991 1990 500 900 3000 Honda 1991 3000 SELECT Make, t. Year, t. Sales FROM Car. Sales. Pivot UNPIVOT (Sales for Year in ([1990], [1991])) t Make 1990 1991 Honda 3000 Acura 500 900

새로운 관계형 연산자 APPLY Name Zip. Code Redmond Sammamish 98052 98074 SELECT * FROM Cities CROSS APPLY Get. Vertices(Zip. Code) Name Zip COde X Y Redmond 98052 13. 5 23. 6 Redmond 98052 14. 0 22. 6 Sammamish 98074 12. 5 24. 5 Sammamish 98074 11. 9 23. 9

재귀 구현 – 일반 쿼리 DECLARE @Rows. Added int -- 누적 결과를 저장할 table 변수 DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = ‘ 12345’ SET @Rows. Added = @@rowcount -- While new employees were added in the previous iteration WHILE @Rows. Added > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1. */ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1. INSERT @reports SELECT e. empid, e. empname, e. mgrid, e. title, 0 FROM employees e, @reports r WHERE e. mgrid=r. empid and e. mgrid <> e. empid and r. processed = 1 SET @Rows. Added = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration. */ UPDATE @reports SET processed = 2 WHERE processed = 1 END

재귀 구현 – 재귀 CTE 사용 WITH Emp. CTE(empid, empname, mgrid) AS ( SELECT empid, empname, mgrid FROM Employees WHERE empid = '12345' UNION ALL SELECT E. empid, E. empname, E. mgrid FROM Employees AS E JOIN Emp. CTE AS M ON E. mgrid = M. empid ) SELECT * FROM Emp. CTE

스냅샷 격리 • 트랜잭션 격리 수준 – – – Read Uncommitted Read Committed Repeatable Read Serializable Snapshot (새로운 격리 수준)

DDL 트리거 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK; CREATE TRIGGER ddl_trig_login ON ALL SERVER FOR DDL_LOGIN_EVENTS AS PRINT 'Login Event Issued. ' SELECT EVENTDATA(). value('(/EVENT_INSTANCE/TSQLCommand/Command. Text)[1]', 'n varchar(max)');

SQL Server 2005에서의 오류 처리 • • SQL 20005에서는 T-SQL로 예외 처리를 하는 기능이 제공됨 오류 포착, 오류 처리, 오류 로깅이 가능함 상세한 오류 정보 액세스가 가능함 TRY…CATCH 구조 구문: BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH { sql_statement | statement_block } END CATCH [; ]