Stop using TOP to page for data from now on use the OFFSET

 

With all the innovation that SQL 2012 bring to us in the T-SQL area we have the offset command.

What is the offset command?

I early version of SQL (before 2012) when we wanted to select out of a table a number of row we all have used the TOP command, the top command is selecting the number applied by the command without the option of defining what to select.

This Query will select the top 10 row that are existing in the where condition.

   1: Select  top 10  BusinessEntityID, SalesQuota, CommissionPct, ModifiedDate

  2: From  Sales.SalesPerson

  3: Where  SalesQuota is not NULL

 

Yes we can try and do some manipulation of Where or Order by to select our data but it had its own limitation.

SQL 2012 bring us the offset option, this command help us to select the number of row we’d like to select and the offset of the row meaning from where it will start and bring us the row that we asked for.

In the next scrip we will create a Demo table populate it with data and select.

   1: -- Create the DEMO DB and use this DB for the demo

  2: Create database Demo

  3: go

  4: use demo

  5: go

  6: -----------------------------------------------------------------

  7: -- Create the table

  8: CREATE table sales (

  9:   salesID INT IDENTITY,

 10:   customerID INT DEFAULT CONVERT (INT, 100000 * RAND ()),

 11:   salesDate DATETIME , --DEFAULT GETDATE (),

 12:   salesAmount MONEY);

 13: go

 14: -----------------------------------------------------------------

 15: -- populate the table with data

 16: DECLARE @count INT

 17: SELECT @count = 0

 18: WHILE (@count < 5000)

 19: BEGIN

 20:   INSERT INTO sales (salesDate, salesAmount)

 21:     VALUES (getdate()-@count, 100 * RAND ());

 22:   SELECT @count = @count + 1

 23: END;

 24: GO

 

After creating the script we can run this 2 select, one with the top option and the other with the offset.

In the top option we have a sub select to help us retrieve the correct rowset to select from.

In the second select we are retrieving the data using the offset command it is essayer and shorter to do so with it.

Script with the top:

   1: select  top 3 salesid, customerID, salesDate, salesAmount

  2: from  sales

  3: where  salesAmount > 25

  4:     and salesAmount not in (  select top 6 salesAmount 

  5:             from sales 

  6:             order by salesAmount desc)

  7: order by salesAmount desc

Script with the offset:

   1: Select salesid, customerID, salesDate, salesAmount 

  2: from sales 

  3: where salesAmount > 25

  4: order by salesAmount desc

  5: offset 6 rows

  6: fetch next 3 rows only