Steve explains data paging with CTE’s

Video transcription:

Basically, what data Paging is, is when you’ve got a large large result set and what you want to do is display like the top 20, or top 50 results. So if you go to any search engine and do a searching on some topic, you you’ll see that you’ll get like the top 20 results out of 2 million or 200,000, or a few 100. What data pages allows you to do is to page through and see those results. Now, I’ve seen this done on different website projects really poorly, a lot of times where somebody will just say SELECT * from a table where the result set matches your search criteria. And the query ends up iterating over like a few 1000 or 100,000 rows, and only displaying the ones that are needed in the webpage, like getting the top 20 And then getting like 21 through 40 and 41 through 60. But each time it’s doing a whole lot of work there. And so many times I’ve seen where this doesn’t scale where it works great in a dev environment where there’s only a few 100 rows. But then as soon as it gets into reality, things really bogged down. And you can really simplify this with CTE’s. There’s a great way to kind of using calculations that are figured out what page you are based off of page sizing and just display the data that you need for that given page without as much work in SQL Server 2012. The OFFSET and fetch keywords were introduced, which is a little bit easier to use than a CTE for data paging and a little bit more efficient. But it doesn’t always work in all environments. So we’re gonna take a look at both ways of doing this to make sure that you understand the pros and cons around both of them. So here’s an example of a data page. And this is selecting from tables and indexes that are in the database and it starts out where page one has rows one through 10. Page two has rows 11 through 20. Page three has rows 21 through 30. And you can do this just by selecting Top 10, Top 20, Top 30 but you’ll find out that the further you go into the pages the slower that it gets

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!