What's new in SQL server 2012

What's New In SQL Server 2012 - OFFSET and FETCH

                                                    Earlier days(SQL Server 2000) hope you all remember when we need to display reports with page break, For a example if you have 100 records but you need to display 5 rows per page then how much difficult to write T/SQL script for that?, hope you all remember following SQL statements,

SQL Server 2000(T-SQL)    

USE tempdb
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblOffsetAndFetch];
END   
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblOffsetAndFetch](
        [id] [int] PRIMARY KEY,
        [name] [varchar](50),
        [cDate] [datetime] NULL,
        [comments] [varchar](200) NULL
    ) ON [PRIMARY];
END
GO
INSERT INTO [dbo].[tblOffsetAndFetch]([id],[name],[cDate],[comments])
SELECT id,name,crdate,name+'| Type - '+xtype FROM sys.sysobjects;
GO
 

--For SQL server 2000
DECLARE @noofrows        smallint;
DECLARE @pages            smallint;
DECLARE @pageNumber        smallint;
DECLARE @ssql            varchar(8000);
SET @noofrows=5;
SET @pageNumber=0;
SET @pages=((SELECT COUNT(*) FROM [dbo].[tblOffsetAndFetch])/@noofrows)+CASE WHEN ((SELECT COUNT(*) FROM [dbo].[tblOffsetAndFetch])%@noofrows)>1 THEN 1 ELSE 0 END;

SELECT * FROM [dbo].[tblOffsetAndFetch] ORDER BY id;

WHILE @pages>@pageNumber BEGIN
    SET @ssql='SELECT TOP '+CAST(@noofrows AS varchar(3))+' * FROM [dbo].[tblOffsetAndFetch] M WHERE M.id NOT IN(SELECT TOP '+CAST(@noofrows*@pageNumber AS varchar(3))+' D.id FROM [dbo].[tblOffsetAndFetch] D ORDER BY id);';
    EXEC(@ssql);
    SET @pageNumber=@pageNumber+1
END;   
GO

After SQL Server 2005 released, it contains many new features like

ROW_NUMBER() according to row_number function you can convert above task 

very simple way as follows,

--For SQL server 2005 and 2008R2

;WITH T (RowNumber,id,name,cDate,comments)
AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY A.id) AS RowNumber,A.id, A.name, A.cDate, A.comments 
  FROM [dbo].[tblOffsetAndFetch] A
 ) 
SELECT * FROM T WHERE RowNumber BETWEEN 0 AND 5;

  
;WITH T (RowNumber,id,name,cDate,comments)
AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY A.id) AS RowNumber,A.id, A.name, A.cDate, A.comments 
  FROM [dbo].[tblOffsetAndFetch] A
 ) 
SELECT * FROM T WHERE RowNumber BETWEEN 6 AND 10; 

Well After SQL Server 2012 released, SQL Server Development team has done many new addition to 

SQL Server like OFFSET and FETCH.With Using above commands you can fulfill above task with following way,

--1st 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

--2nd 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

--3rd 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Now you will notice how much easy with new "OFFSET and FETCH" commands in SQL server. 

mySQL you have limit function and PL/SQL rownumber column you can help to do above task in same output results. 

But this time I hope SQL server more smarter than Oracle(Im not sure about new PL/SQL commands in Oracle 12c) 

Let me explain about execution plan for this,

Well! now you can realize how much performance impact on SQL server new commands.

Cheers... 
and Most welcome your comments and ideas...