After dealing with TOP and ROWCOUNT in SQL Server, there is one more way of getting a limited amount of data from a query that you should be aware of – OFFSET/FETCH. It can be a little bit more difficult to set up and work with but if you’re in need of the data a certain way then the OFFSET/FETCH method is definitely a great option.
First, we’ll start off with the data that we’ve worked with several times before – Nirvana Performances:
SELECT *
FROM dbo.NirvanaPerformances
ORDER BY TRY_CONVERT(DATE, ConcertDate) DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;Then we’ll add two new items that I talked about earlier: OFFSET and FETCH.
The OFFSET tells SQL Server where to start pulling the data that we want to see. For example, if we want to start pulling at row 100, then we would tell it OFFSET 100 ROWS. Please remember that if you want to start your data pull as you normally would, you must tell it to offset it by 0 rows.
In the FETCH function, you tell it how many rows you want to grab. For example, if I want 100 rows, I’ll tell it FETCH NEXT 100 ROWS ONLY.
SQL Server will accept either of these to be ROW instead of ROWS. I tend to stay with ROWS just because the only time it reads that way in English is at 1 row and I rarely offset by only 1 row.
You do have to include both OFFSET and FETCH if you want to tell SQL Server how many rows you want in the FETCH statement. But, you do not need to tell it how many rows to FETCH if you want to use OFFSET. For example, this code skips the first 150 rows of Nirvana Performances but shows every row after that.
SELECT *
FROM dbo.NirvanaPerformances
ORDER BY TRY_CONVERT(DATE, ConcertDate) DESC
OFFSET 150 ROW;In this example, you’ll note there is a slight difference to the query that we first showed.
SELECT *
FROM dbo.NirvanaPerformances
ORDER BY TRY_CONVERT(DATE, ConcertDate) DESC
OFFSET 0 ROW
FETCH FIRST 50 ROW ONLY;Instead of saying “FETCH NEXT”, we used “FETCH FIRST”. What is the difference? Absolutely nothing. According to the SQL Server documentation, both of them are included to give SQL Server ANSI compatibility. So, my dear friend, you can use whichever makes the most sense to you with no fear that it will do something different for one and not the other.
Another great reason to use OFFSET/FETCH is that you can use variables and subqueries as I have previously shown for TOP here.
With that, my friends, I have come to the conclusion of my series on TOP. Hopefully, you have learned a fair bit about what can be done with TOP and a few other ways to get to the data if TOP doesn’t quite work for you. Until next time dear readers!