향상된 TOP 기능
SQL 7.0과 2000
상수만 사용 가능
SELECT TOP <상수값>
SQL 2005 : TOP (<식>)
TOP에 변수 사용 가능
구문
TOP (expression) [PERCENT]
[ WITH TIES ]
<식>은 변수, 서브쿼리, 상수가 될 수 있음
INSERT, UPDATE, DELETE 에도 사용 가능함
쿼리 옵티마이저가 실행 계획 작성 시 고려 대상임
OUTPUT 절
DML 연산의 일부로서, 행을 반환하는 기능임
작업의 결과를 확인 가능함
“Inserted” 와 “Deleted” 를 사용하여 업데이트 전후의 데이터를 확인 가능함
결과 행을 테이블 또는 변수에 저장 가능함
OUTPUT … INTO …
예:
USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO
EXCEPT와 INTERSECT
두 개의 서로 다른 행 집합들을 기반으로 결과 집합을 제공하는 집합 연산자임
EXCEPT
첫 번째 결과 집합에는 존재하고 두 번째 결과 집합에는 존재하지 않는 행들의 집합을 반환함
INTERSECT
양쪽 집합에 모두 존재하는 행들의 집합을 반환함
전제 조건
모든 쿼리의 컬럼들의 수와 순서가 동일해야 함
데이터 타입이 호환되어야 함
예:
SELECT * FROM TableA EXCEPT SELECT * FROM TableB;
SELECT * FROM TableA INTERSECT SELECT * FROM TableB;
UNION, EXCEPT, INTERSECT
다른 T-SQL문과 연계 사용 시 가이드
INTO 절을 사용하여 최종 결과 집합을 테이블에 저장하고자 하는 경우에는 첫 번째 쿼리에만 INTO를 기술함
ORDER BY는 SQL문의 마지막 부분에 기술함
GROUP BY와 HAVING 절은 개별 쿼리에만 사용 가능하며, 최종 결과 집합에는 영향을 미치지 않음
Common Table Expression
명명된 테이블 식
구문:
WITH <CTEName> ( <col-list> )
AS ( <CTE>) <SELECT, INSERT, UPDATE or DELETE using CTEname>
WITH 절은 다음에 이어지는 SELECT/INSERT/DELETE/UPDATE 구문과 한 문장임
단일 WITH 절에서 다중의 CTE를 하나의 T-SQL 문에 정의 가능함
두 가지 유형
재귀 CTE : 쿼리에서 재귀 호출이 가능하도록 해 줌
비재귀 CTE : 파생된 테이블과 뷰의 특성이 혼합된 기능임
Common Table Expression 재귀 용법
SQL Server에서 CTE를 도입한 주된 동기 : 재귀
장점
복잡한 SQL 문의 이해와 관리가 용이해짐
뷰와 유사하며 파생된 테이블과 흡사함
재귀 구현이 용이함
단일 쿼리로 재귀 계층 구조를 검색 가능함
전형적인 시나리오:
테이블에 존재하는 계층 구조 (NGRID-EMPID, 부품-서브부품)
예:
특정 매니저에게 리포팅하는 모든 직원 또는
어떤 제품을 조립하는데 필요한 모든 부품
<CTE>가 자신을 참조 --> 재귀
CTE의 재귀 형식
<비재귀 SELECT> <-- 초기화
UNION ALL
<CTE를 참조하는 SELECT> <-- 누계
두 번째 SELECT문의 결과가 없을 때 재귀 호출이 중단됨
재귀 구현 – 일반 쿼리
DECLARE @RowsAdded 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 @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 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 @RowsAdded = @@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 EmpCTE(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 EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
스냅샷 격리
트랜잭션 격리 수준
Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot (새로운 격리 수준)
데이터를 조회할 때 유용하게 사용됨
OLTP 환경에서 블로킹을 유발하지 않고 지속적인 읽기가 가능함
쓰기 작업이 읽기 작업을 블로킹하지 않고, 읽기 작업이 쓰기 작업을 블로킹하지 않음
쓰기 작업은 상대적으로 적고 읽기 작업이 주로 발생하는 데이터베이스에 최적임
타 데이터베이스로부터의 이전이 용이해짐
DDL 트리거
DDL(Data Definition Language) 이벤트에 대한 지원이 포함된 트리거의 확장 버전
모든 DDL 문장을 포함하는 이벤트에 대한 트리거
CREATE, ALTER, DROP 등의 이벤트
이벤트 그룹
DDL_DATABASE_LEVEL_EVENTS
Eventdata() 함수 사용 가능
Eventdata() : 서버 또는 데이터베이스 이벤트에 대한 정보를 반환함
DDL 트리거의 Body에 Eventdata() 함수 사용 가능
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/CommandText)[1]','nvarchar(max)');
[출처] SQL Server 2005 TSQL - 4.. 기타|작성자 선경
