CTE란 SELECT 쿼리에 기반한 명명된 임시적인 결과 셋입니다.결과 셋은INSERT,UPDATE, DELETE, SELECT에서 사용되어 질 수 있습니다.
이는 크게 단순 CTE 와 재귀CTE로 나누어 집니다. 단순CTE는 마치 파생테이블을 사용하는 개념과 유사하다 하겠습니다.
SQL Server 2000에서 재귀관계를 표현하기 위해 루프를 이용하던 것을 SQL Server 2005 에서의 재귀CTE로 바꾸어 보도록 하겠습니다. 부하직원이 누구인지 알아보는 재귀관계 입니다.
[따라하기] SQL Server 2000 과 SQL Server 2005에서의 top 비교
[WITH <common_table_expression> [,...n] ]
<common_table_expression>::=
expression_name
[(column_name [,...n])]
AS
(<CTE_query_expression>)
■ SQL 단순 CTE 생성 절차
- CTE 이름과 컬럼 리스트를 선택합니다
- CTE SELECT 쿼리를 만듭니다
- 쿼리에서 CTE를 사용합니다
[따라하기] SIMPLE CTE 사용하기
1.CTE 이름을 TopSales로 정합니다.
2. 다음 쿼리에서 CTE를 테이블처럼 사용합니다
WITH TopSales (SalesPersonID, NumSales) AS
( SELECT SalesPersonID, Count(*)
FROM Sales.SalesOrderHeader GROUP BY SalesPersonId )
SELECT * FROM TopSales
WHERE SalesPersonID IS NOT NULL
ORDER BY NumSales DESC
■ 재귀CTE 생성절차
- CTE SELECT쿼리안의 추가적인 고려사항을 제외하면 단순CTE와 비슷합니다
- 앵커 멤버 쿼리(ANCHOR MEMBER QUERY)를 생성합니다(트리에서 꼭대기 부분이 됩니다)
- union all 연산자를 사용합니다: 재귀CTE에서 두개의 결과 셋을 연결하기 위해 꼭 필요하며 유일하게 허용된 집합연산자입니다
- CTE를 스스로 참조하는 재귀 멤버 쿼리(Recursive Member Query)를 만듭니다
■ SQL Server 2000에서의 재귀관계
CREATE PROC emp_two
AS
DECLARE @empTemp TABLE(
empid INT,
mgrid INT,
lvl INT
)
DECLARE @lvl INT
SET @lvl=0
INSERT @empTemp
SELECT employeeid,managerid,@lvl
FROM HumanResources.Employee
WHERE ManagerID IS NULL
WHILE @@ROWCOUNT >0
BEGIN
SET @lvl=@lvl+1
INSERT @empTemp
SELECT e.employeeid,e.managerid,@lvl
FROM HumanResources.Employee e join @empTemp t
ON e.managerID = t.empid and t.lvl=@lvl-1
END
SELECT mgrid,empid FROM @empTemp
ORDER BY mgrid,empid
GO
SQL Server 2000결과셋 - 290개의 행
■ 재귀CTE를 이용한 SQL Server 2005에서의 재귀관계
CREATE PROC ctetest
AS
WITH managers AS
( SELECT ManagerID,employeeid
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID,e.EmployeeID
FROM HumanResources.Employee e
INNER JOIN Managers mgr
ON e.Managerid=mgr.employeeid)
SELECT * FROM managers ORDER BY managerid,employeeid
GO
SQL Server 2005 결과셋 - 290개의 행
|
순위를 구하기 위해 SQL Server 2000에서 상관 서브 쿼리 방식 또는 함수를 이용하는 경우가 많았습니다. SQL Server 2005에서는 순위를 구하는 rank( )함수가 따로 만들어져 있습니다
rank( )함수의 경우 같은 값인경우 동순위로 순위가 매겨집니다.
[문법]
RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
[따라하기] SQL Server 2000 과 SQL Server 2005에서의 순위구하기 비교
1.SQL Server 2000에서 상관서브쿼리를 이용하는 경우
USE AdventureWorks;
GO
SELECT productid,quantity,
(SELECT COUNT(quantity) + 1
FROM Production.ProductInventory
WHERE p.quantity < quantity) rank
FROM Production.ProductInventory p
ORDER BY rank
2.SQL Server 2000에서 함수를 이용하는 경우
CREATE FUNCTION dbo.uf_quan_rank(@qu INT)
RETURNS INT
AS
BEGIN
RETURN(SELECT COUNT(quantity) +1
FROM Production.ProductInventory p
WHERE @qu <p.quantity)
END
SELECT productid,quantity,dbo.uf_quan_rank(quantity) AS rank
FROM Production.ProductInventory
ORDER BY rank
3.SQL Server 2005에서 rank( )함수 이용하기
SELECT productid,quantity,RANK( ) OVER (ORDER BY quantity DESC) AS rank
FROM Production.ProductInventory
ORDER BY rank
SQL Server 2005에서 rank( ) 함수
|
순위를 매기는 것이 아니라 단순히 데이터를 나열하여 원하는 데이터 개수만큼 가져와야 하는 경우가 많습니다. 이를 위해 임시 테이블에 identity속성을 이용하는 방법을 흔히 사용합니다. 이를 SQL Server 2005의 새로운 함수 row_number( )함수로 바꾸어 보고자 합니다 이는 또한 페이징을 위한 좋은 방안중 하나가 될 수도 있습니다
[문법]
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) [따라하기] SQL Server 2000 과 SQL Server 2005에서의 번호 나열하기 비교
1.SQL Server 2000에서 번호를 구하려는 경우
CREATE TABLE #temp
(id INT IDENTITY ,
SalesOrderID INT,
OrderDate DATETIME
)
INSERT INTO #temp
SELECT SalesOrderID,OrderDate
FROM Sales.SalesOrderHeader
ORDER BY orderDate ASC
SELECT *
FROM #temp
WHERE id BETWEEN 50 AND 60
SQL Server 2000에서 번호 매기기
2.SQL Server 2005에서 row_number( )함수를 이용하여 번호를 구하는 경우
SELECT *
FROM(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER( ) OVER (ORDER BY OrderDate)AS RowNumber
FROM Sales.SalesOrderHeader
) a
WHERE a.rowNumber between 50 and 60
3.SQL Server 2005에서 row_number( )함수 와 CTE를 이용하여 번호를 구하는 경우
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER( ) OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
SQL Server 2005에서 rownumber( )함수
|
SQL Server 2000에서는 행 별로 표현된 값을 컬럼 이름으로 가져가기 위해서 CASE문을 써야만 하였습니다. 그러던 것을 SQL Server 2005에서는 PIVOT연산자를 이용하여 간단하게 구현할 수 있습니다
[문법]
SELECT * FROM table_source
PIVOT ( aggregate_function ( value_column )
FOR pivot_column
IN ( <column_list> )
) table_alias ■ SQL Server 2000에서의 데이터 통계 구하기
1.쿼리
SELECT DATEPART(yy,ModifiedDate)as 년도,
SUM(CASE DATEPART(qq,ModifiedDate) WHEN 1 THEN 1 ELSE 0 END)AS [1사분기],
SUM(CASE DATEPART(qq,ModifiedDate) WHEN 2 THEN 1 ELSE 0 END)AS [2사분기],
SUM(CASE DATEPART(qq,ModifiedDate) WHEN 3 THEN 1 ELSE 0 END)AS [3사분기],
SUM(CASE DATEPART(qq,ModifiedDate) WHEN 4 THEN 1 ELSE 0 END)AS [4사분기]
FROM Sales.SalesOrderDetail
GROUP BY DATEPART(yy,ModifiedDate)
ORDER BY DATEPART(yy,ModifiedDate)
2.결과값
■ SQL Server 2005에서 PIVOT를 이용한 데이터 통계
1.쿼리
WITH quauter AS
(SELECT DATEPART(yy,ModifiedDate) AS YEAR ,
DATEPART(qq,ModifiedDate) AS qua
FROM Sales.SalesOrderDetail)
SELECT *
FROM quauter
PIVOT(COUNT(qua) FOR qua IN([1],[2],[3],[4]))pvt 2.결과값
|
SQL Server 2005는 구조화된 예외처리 방법을 가지게 되었습니다. 이는 많은 T-SQL문에서 중요한 요구사항이었습니다.특히 트랜잭션구문에서 그러했습니다. 많은 다른 언어에서 사용하는 예외처리와 유사한 방식입니다
SQL Server 2000에서 오류 확인은 @@error 시스템함수를 많이 이용하였습니다. 이를 SQL Server 2005에서의 TRY..CATCH구문으로 바꾸어 처리하여 봅시다
■ SQL Server 2000구문에서의 에러 처리 방식
- ta테이블을 만듭니다
- 자식 프로시저 proc2를 생성합니다 수정문 과 삽입문으로 이루어져 있습니다. 삽입문은 기본키 제약조건에 위배되므로 에러가 발생합니다
- 부모 프로시저 proc1을 생성합니다. 하나의 트랜잭션 내에서 테이블ta로 삽입하는 문장과 프로시저 proc2를 호출합니다. proc2에서 기본키 위반 오류가 발생을 하여도 이
를 호출한 부모 프로시저로 오류 발생 사실이 전달 되지 않아 트랜잭션 전체가 롤백되지 못하고 오류 처리 문장이 실행되지 않습니다
- pubs데이터베이스의 titles테이블 내용
1. 프로시저를 생성합니다
USE pubs
GO
CREATE TABLE dbo.ta(id INT)
Go
CREATE PROC dbo.proc2
AS
UPDATE titles
SET title=’이걸로 수정됨’
WHERE title_id=’bu1111’
INSERT INTO titles(title_id,title) VALUES‘( bu1032’,’happpy’)
IF @@error <>0
BEGIN
RETURN
END
GO
CREATE PROC dbo.proc1
AS
DECLARE @err INT
BEGIN TRAN
INSERT INTO dbo.ta VALUES(1)
EXEC proc2
SELECT @err = @@error
PRINT‘ proc1 error code=‘ + cast(@err AS VARCHAR(10))
IF(@err <>0)
BEGIN
PRINT‘ proc1 오류처리 롤백’
ROLLBACK TRAN
RETURN
END
COMMIT TRAN
GO
EXEC proc1 --프로시저 생성
[참고 오류메시지]
(1개 행 적용됨)
(1개 행 적용됨)
메시지 2627, 수준 14, 상태 1, 프로시저 proc2, 줄 7 PRIMARY KEY 제약 조건‘UPKCL_titleidind’을(를) 위반했습니다. 개체‘dbo.titles’에 중복 키를 삽입할 수 없습니다.
문이 종료되었습니다.
proc1 error code= 0
2. 데이터를 확인하여 봅니다
3.proc2프로시저에서 raiserror( )문을 사용하기
이렇게 하면 부모프로시저로 오류가 전달되어 부모 프로시저에서 오류 확인 처리를 하게 됩니다. SQL Server 2000에서는 이와 같은 에러 처리 확인 방식을 사용했습니다.
ALTER PROC dbo.proc2
AS
UPDATE titles
SET title=’이걸로 수정안됨‘
WHERE title_id=’bu1111’
IF @@ERROR<>0
BEGIN
RAISERROR‘( proc2 update에서 에러 발생’,16,1)
RETURN
END
INSERT INTO titles(title_id,title) VALUES‘( bu1032’,’happpy’)
IF @@ERROR <>0
BEGIN
RAISERROR‘( proc2 insert 에서 에러 발생’,16,1)
RETURN
END
GO
[참고 오류메시지]
(1개 행 적용됨)
(1개 행 적용됨)
메시지 2627, 수준 14, 상태 1, 프로시저 proc2, 줄 13
PRIMARY KEY 제약 조건‘UPKCL_titleidind’을(를) 위반했습니다. 개체‘dbo.titles’에
중복 키를 삽입할 수 없습니다.
문이 종료되었습니다.
메시지 50000, 수준 16, 상태 1, 프로시저 proc2, 줄 16
proc2 insert 에서 에러 발생
proc1 error code= 50000
proc1 오류처리 롤백
■ SQL Server 2005의 TRY..CATCH구문
[문법]
BEGIN TRY
{sql_statement | statement_block}
END TRY
BEGIN CATCH
{sql_statement | statement_block}
END CATCH
1.SQL Server 2005의 TRY..CATCH구문을 이용한 오류코드 처리 예제
USE pubs
GO
CREATE PROC dbo.procb
AS
UPDATE titles
SET title=’happy’
WHERE title_id=’bu1111’
INSERT INTO titles(title_id,title) VALUES‘( bu1032’,’happpy’)
IF @@ERROR <>0
BEGIN
RETURN
END
GO
CREATE TABLE dbo.tb(id INT)
GO
CREATE PROC dbo.proca
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.tb VALUES(1)
EXEC procb
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @error INT
SELECT @error = @@error
ROLLBACK TRAN
PRINT‘ proca error code=‘ + CAST(@error AS VARCHAR(10))
END CATCH
GO
EXEC proca
[참고 오류메시지]
(1개 행 적용됨)
(1개 행 적용됨)
proca error code= 2627
2.데이터를 확인하여 봅니다.
원하는 대로 오류확인을 하였고 트랜잭션 전체가 롤백 되었습니다
업그레이드 후 성능 자료 수집
업그레이드 이후 windows의 성능 카운터를 통해 전체적인 성능향상 자료를 수집할 수 있을 것입니다. 또한 SQL Server 2005를 이용하여 개개의 쿼리를 수정한 이후 실행계획,수행시간,IO(Input Output)시간 등의 자료를 수집할 수 있을 것입니다. 쿼리에 대한 분석은 sql server management studio 또는 프로필러를 이용할 수 있습니다 |
