A common requirement for applications is to display a list – that can range from products to search results. While showing all entries works for small lists, it is not that useful for lists with hundreds of entries. In this case you should use paging and only show 10 or 20 entries at a time. The user sees the first part of entries and then can switch to the next page of the list to get more entries.
This approach is faster, reduces the workload on your systems and the user isn’t drowning in data. Let’s look how you can implement paging in applications that use SQL Server.
SQL Server 2012+
SQL Server needed some time to offer a simple way to get paging working. If you can use SQL Server 2012 or later, this simple syntax gets you a paged result:
1 2 3 4 5 6 7 8 9 |
SELECT [Id] ,[FirstName] ,[LastName] ,[Email] FROM [Users] WHERE [Subscribed] = 1 Order By [Id] OFFSET @offset ROWS FETCH NEXT @rowsPerPage ROWS ONLY |
With OFFSET you specify how many rows should be skipped. If you skip 0 rows you get the first ones in the list. FETCH NEXT is used to specify how many rows you want.
The syntax above uses the placeholders @offset and @rowsPerPage to use this query in a prepared statement. To get the first 20 entries you can use this code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = new SqlCommand(selectQuery, connection); var offset = new SqlParameter("@offset", SqlDbType.Int, 0); offset.Value = 0; command.Parameters.Add(offset); var rowsPerPage = new SqlParameter("@rowsPerPage", SqlDbType.Int, 0); rowsPerPage.Value = 20; command.Parameters.Add(rowsPerPage); command.Prepare(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine($"{reader[1]} {reader[2]} - {reader[3]}"); } } |
If you use Dapper, this command does the same with much less code:
1 2 3 4 |
var result = conn.Query<User>(selectQuery, new {offset = 0, rowsPerPage = 20} ).ToList(); |
Older versions of SQL Server
Without the new syntax, you need to use a far more complex query to get your paged results from SQL Server:
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY Id ) AS RowNum ,[Id] ,[FirstName] ,[LastName] ,[Email] FROM Users WHERE [Subscribed] = 1 ) AS RowConstrainedResult WHERE RowNum > @start AND RowNum <= @end ORDER BY RowNum |
Conclusion
The syntax for SQL Server 2012 and newer is much simpler to understand. But when you can’t use a current version of SQL Server you still are able to page your results on the database server.