An Order By Puzzle.sql

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: [email protected]

Copyright (c) 2018 SQL Workbooks LLC

Permission is hereby granted, free of charge, to any person 
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without 
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom 
the Software is furnished to do so, subject to the following 
conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 
OTHER DEALINGS IN THE SOFTWARE.
*****************************************************************************/

USE master;
GO
IF DB_ID('OrderByPuzzle') IS NOT NULL
BEGIN
    ALTER DATABASE OrderByPuzzle SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE OrderByPuzzle;
END
GO
CREATE DATABASE OrderByPuzzle;
GO
USE OrderByPuzzle;
GO

DROP TABLE IF EXISTS dbo.Test;
GO

CREATE TABLE dbo.Test (
    Id BIGINT PRIMARY KEY CLUSTERED,
    SecondInt BIGINT,
    Extra INT
);
GO

--This query adapted from pattern attributed 
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
--Generates 1 million rows
WITH cte1(num) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), 
cte2(num) AS (SELECT 1 FROM cte1 CROSS JOIN cte1 AS b),
cte3(num) AS (SELECT 1 FROM cte2 CROSS JOIN cte2 AS b),
cte4(num) AS (SELECT 1 FROM cte3 CROSS JOIN cte3 AS b)
INSERT  dbo.Test (Id, SecondInt, Extra)
SELECT TOP (1000000)
	ROW_NUMBER() OVER (ORDER BY (SELECT num)) as Id, 
    100 * ROW_NUMBER() OVER (ORDER BY (SELECT num)) as SecondInt, 
    555 as Extra
FROM cte4;
GO

/* Query 1 */
SELECT Id 
FROM dbo.Test 
ORDER BY Extra DESC;
GO

/* Query 2 */
SELECT TOP 2 Id 
FROM dbo.Test 
ORDER BY Extra DESC;
GO

/* Query 3 */
SELECT Id 
FROM dbo.Test 
ORDER BY Extra DESC 
    OFFSET 0 ROWS 
    FETCH NEXT 2 ROWS ONLY;
GO

/* Query 4 */
DECLARE 
    @PageSize INT = 2,
    @Page INT = 1;

SELECT Id 
FROM dbo.Test 
ORDER BY Extra DESC 
OFFSET @PageSize * (@Page - 1) ROWS 
FETCH NEXT @PageSize ROWS ONLY;
GO

/* Query 4 + RECOMPILE */
DECLARE 
    @PageSize INT = 2,
    @Page INT = 1;

SELECT Id 
FROM dbo.Test 
ORDER BY Extra DESC 
OFFSET @PageSize * (@Page - 1) ROWS 
FETCH NEXT @PageSize ROWS ONLY
    OPTION (RECOMPILE);
GO