SQL Server Offset Pagination
When you have few rows in your tables, you may be tempted to return all rows to an API or Client call, and let that layer deal with pagination. This works, till a certain tipping point, after which calls will become slow and your APIs are cursed at and your UIs cussed out by users who're waiting and waiting. You can avoid this in different ways, and one of my favorite methods is to paginate right from the database, with a cap of how many rows you will return in a call. Coupled with good indexing on the columns the API or UI requests the sorting/ordering on, you can scale better. Let's look at a simple example of Offset pagination. Step 1 = Create Table for our demo CREATE TABLE dbo . OffsetPaginationUsers ( UserId integer not null identity Primary Key , FirstName varchar ( 255 ) not null, LastName varchar ( 255 ) not null ) Step 2 = Add dummy rows IN SERT INTO dbo . OffsetPaginationUsers ( FirstName , LastName ) SELECT TOP 100 Nam...