Escape to the Sky

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...

Multi-Statement Table Valued Functions (MSTVF) MSTVFs can be useful, when the number of rows returned is about 100 or less. Since SQL Server cannot estimate the returned rowcount correctly, it defaults to a row estimate of 1 or 100 depending on the SQL Server version. Even if the MSTVF returns a lot of rows, SQL Server opts for serial execution rather than parallel, due to this underestimation problem. Let's look at a simple example (SQL Server 2019 running on Compat Level 130) I am not using interleaved execution or any new fancy features for this demo, which are supposed to help improve performance. Step 1 = Change Cost Threshold For Parallelism (CTFP) WARNING - DO NOT RUN THIS CTFP configuration change ON ANY SERVER without knowing the  consequences ****. What we're doing is reducing the Query Cost, at which SQL Server  will decide to go for parallel execution. This is done for this demo, to ensure that  SQL Server will go parallel at a lower cost than usual E XEC sp_...