This is how I like to do paging with T-SQL. I've seen quite alot of ways to do paging with SQL Server, both with and without temporary tables and this is definetly my favorite as I find it both straight forward and easy to read. And yes, this i mainly a note to self :-)
DECLARE @intStartRow int; DECLARE @intEndRow int; SET @intStartRow = (@intPage -1) * @intPageSize + 1; SET @intEndRow = @intPage * @intPageSize; WITH blogs AS (SELECT strBlogName, ROW_NUMBER() OVER(ORDER BY intID DESC) as intRow, COUNT(intID) OVER() AS intTotalHits FROM tblBlog) SELECT strBlogName, intTotalHits FROM blogs WHERE intRow BETWEEN @intStartRow AND @intEndRow
The example above assumes that you have a table namned tblBlog that contains a column named strBlogName. It also assumes that the variables @intPage and @intPageSize are already declared and set, usually as parameters to a stored procedure.
The first four rows in the example declares two new variables, @intStartRow and @intEndRow and calculates their values based on @intPage and @intPageSize.
The seven last rows are the interesting ones. In them we first specify a subquery which in which we specify the columns we want to select (strBlogName in the example), that we want to calculate the row number based on a specific sort order (intID DESC in the example) and also that we want to calculate the total number of rows that the subquery will select. It is also here that we do any filtering, such as WHERE strBlogName LIKE '%a%'.
Finally we select the columns that we want to select plus the column with the total number of rows that the subquery returns and filter out only the rows that belongs to the specified page.
If anyone know of a better way I'd love to know :-)
- Dynamic sorting with T-SQL
- Getting the first day in a week with T-SQL
- My development toolbox
- A simple example of the Open/Closed Principle
- Inversion of Control – An Introduction with Examples in .NET
- The DRY obsession
- Learning Scala part five - Methods
- Learning Scala part three – Executing Scala code