1 with result sets

2 offset and fetch

3 throw in error handling

4 sequence

5 top over

6 참고자료

create procedure denali_withresultset
as
select 1 as no,'tsql' type, 'withresultset' as feature union all
select 2 as no,'tsql' type, 'throw' as feature union all
select 3 as no,'tsql' type, 'offset' as feature union all
select 4 as no,'tsql' type, 'sequence' as feature 
go

exec denali_withresultset 
with result sets
(
       (      no int,
              featuretype varchar(50),
              featurename varchar(50)
       )  
) 


--결과가 다음과 같이 나오게 함
/*
no featuretype featurename
1  tsql        withresultset
2  tsql        throw
3  tsql        offset
4  tsql        sequence
*/

select productid, name    
from adventureworks.production.product
order by name 
offset 10 rows
fetch next 5 rows only

select productid, name 
from ( 
select row_number() over(order by name) as rowid, productid, name 
from adventureworks.production.product 
) x 
where rowid between 11 and 15 
order by rowid 

select productid, name    
from adventureworks.production.product
order by name 
--offset 10 rows
fetch first 5 rows only

THROW 50001, 'Error message', 1;

begin try 
  select 'using throw'
  select 1 / 0  
end try 
begin catch 
  --throw error 
  throw
end catch

(1 row(s) affected)

 (0 row(s) affected)

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

create sequence dbo.seq 
as int
start with 1
increment by 1;

SELECT NEXT VALUE FOR dbo.Seq;
SELECT NEXT VALUE FOR dbo.Seq;
SELECT NEXT VALUE FOR dbo.Seq;

--결과는 차례로 1,2,3 나온다.

create table dbo.examp1
(
  seq int not null,
  name varchar(50) not null
);

create table dbo.examp2
(
  seq int not null,
  name varchar(50) not null  
);


insert into dbo.examp1(seq, name) values(next value for dbo.seq, 'tom');
insert into dbo.examp2(seq, name) values(next value for dbo.seq, 'jerry');

select * from examp1 --결과 4, tom
select * from examp2 --결과 5, jerry

SELECT TOP (3) OVER(PARTITION BY custid
ORDER BY val DESC)
orderid, orderdate, custid, empid, val
FROM Sales.OrderValues;

SELECT TOP (3, PARTITION BY custid
ORDER BY val DESC)
orderid, orderdate, custid, empid, val
FROM Sales.OrderValues;

• New T-SQL Features in SQL Server 2011