SQL Server 2005 introduced ROW_NUMBER() which provided an easy option to implement server side paging. SQL Server 2012 added another enhancement which made paging much simpler and efficient.

All the scripts in this demo assume that a database with name **NorthPole** exists in the current SQL Server instance. In case you do not have it, just create an empty database with the same name or edit the script below to point to a database where you would like to run the scripts.

	USE NorthPole 
	GO

	IF OBJECT_ID('Customers','U') IS NOT NULL
		DROP TABLE Customers

	CREATE TABLE Customers (
		CustomerID INT,
		CustomerNumber CHAR(4),
		CustomerName VARCHAR(50),
		CustomerCity VARCHAR(20) )
	GO

	INSERT INTO Customers (
		CustomerID, CustomerNumber, CustomerName, CustomerCity
	)
	SELECT
		Number, 
		REPLACE(STR(Number, 4), ' ', '0'),
		'Customer ' + STR(number,6),
		CHAR(65 + (number % 26)) + '-City'
	FROM master..spt_values WHERE type = 'p' 
		AND number BETWEEN 0 AND 999

The above script will insert 1000 rows to the **Customers** table. We will use this data to run the demo scripts given below.

SQL Server 2005 introduced `ROW_NUMBER()` which provided an easy way to generate a sequence number in a result set, which was quite helpful to write queries that perform server-side-paging. In SQL Server 2005 and 2008, we can write a query such as the one given below.

	USE NorthPole  
	GO

	DECLARE @page INT, @size INT
	SELECT @page = 3, @size = 10

	;WITH cte AS (
		SELECT  TOP (@page * @size)
			CustomerID, 
			CustomerName, 
			CustomerCity,
			ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,
			COUNT(*) OVER(PARTITION BY '') AS Total
		FROM Customers
		WHERE CustomerCity IN ('A-City','B-City')
		ORDER BY CustomerName ASC
	)
	SELECT
		*
	FROM cte
	WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
	ORDER BY seq

	/*
	CustomerID  CustomerName    
	----------- ----------------
	260         Customer 260 
	261         Customer 261 
	286         Customer 286 
	287         Customer 287 
	312         Customer 312 
	313         Customer 313 
	338         Customer 338 
	339         Customer 339 
	364         Customer 364 
	365         Customer 365 
	*/

The above query returns 10 rows from the **customers** table. The query filters customers by the city specified and sorts the results by Customer name. Finally, rows from position 21 to 30 is returned.

SQL Server 2012 provides an easier syntax to achieve the above. We can use `OFFSET` to specify the starting position of the rows and `FETCH NEXT N` to specify the number of rows to retrieve from the `OFFSET`. Here is the new version of the above query.

	USE NorthPole  
	GO

	DECLARE @page INT, @size INT
	SELECT @page = 3, @size = 10

	SELECT 
		*,
		COUNT(*) OVER(PARTITION BY '') AS Total
	FROM Customers
	WHERE CustomerCity IN ('A-City','B-City')
	ORDER BY CustomerID 
	OFFSET (@page -1) * @size ROWS
	FETCH NEXT @size ROWS ONLY;

You may notice a performance difference between both versions of the query. When running both queries on the given sample data on my laptop, I am seeing that the approach using `ROW_NUMBER()` takes 60% of execution time and the new approach (using `OFFSET FETCH NEXT` takes only 40%).

Keep in mind that the test data used in this demo is very simple and will be quite different from production scenarios. So it is important to run tests on your own test data with specific work load and server configuration before accurately assuming the performance statistics of each approach.