{"id":101207,"date":"2024-03-25T10:08:29","date_gmt":"2024-03-25T10:08:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101207"},"modified":"2026-04-14T11:40:58","modified_gmt":"2026-04-14T11:40:58","slug":"pagination-and-ordering-by-large-values","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/pagination-and-ordering-by-large-values\/","title":{"rendered":"SQL Server Pagination Performance: Sorting by Large Text Columns"},"content":{"rendered":"\n

SQL Server’s OFFSET<\/code>\/FETCH<\/code> pagination works efficiently when sorting by indexed integer or date columns – but performance degrades severely when the ORDER BY<\/code> column is a large text field such as varchar(max) or nvarchar(max). SQL Server cannot index LOB<\/code> columns directly, so sorted pagination forces a full scan and sort of the large column data. The most effective fix is to add a computed column that captures the first N characters of the large column, index that computed column, and sort by it instead. A non-clustered columnstore index is an alternative that can compensate in some workloads. This article demonstrates both approaches with a one-million-row test table and execution plan comparisons.<\/strong><\/p>\n\n\n\n

Pagination is a technique for limiting output. Think of Google search results, shopping the electronics category on Amazon, or browsing tagged questions on Stack Overflow. Nobody could consume all of the results in a single shot, and no site wants to spend the resources required to present them all to us, so we are offered a manageable set of results (a “page”) at a time.<\/p>\n\n\n\n

In the most basic cases, we can implement this functionality in SQL Server by using OFFSET<\/code>\/FETCH<\/code>. The problem is that anything that uses TOP<\/code> or OFFSET<\/code> will potentially have to scan everything in the index up until the page requested, which means that queries become slower and slower for higher page numbers. To achieve anything close to linear performance, you need to have a narrow, covering index for each sort option, or use columnstore as Erik Darling recommends here<\/a>, or concede that some searches are just going to be slow. Throw in additional filtering, pulling data from other tables, and letting users dictate any sort order they want, and it becomes that much harder to tune for all cases.<\/p>\n\n\n\n

I have a lot that I want to say about paging, and I will follow up with more content soon. Several years ago, I wrote about some ways to reduce the pain here<\/a>, and it is long overdue for a refresh. For today’s post, though, I wanted to talk specifically about pagination when you have to order by large values<\/strong>. By “large” I mean any data type that can’t fit in an index key, like nvarchar(4000)<\/code> or, really, anything that can’t lead in an index and\/or would push the key past 1,700 bytes.<\/p>\n\n\n\n

An Example<\/h3>\n\n\n\n

Let’s say we have a Users<\/code> table on SQL Server 2022:<\/p>\n\n\n\n

 CREATE TABLE dbo.Users\n (\n   UserID       int IDENTITY(1,1) NOT NULL,\n   CreationDate datetime2(0) NOT NULL\n                DEFAULT sysutcdatetime(),\n   DisplayName  nvarchar(200),\n   Reputation   int NOT NULL\n                DEFAULT 0,\n   Email        nvarchar(320),\n   AboutMe      nvarchar(4000),\n   CONSTRAINT PK_Users PRIMARY KEY(UserId)\n );<\/pre>\n\n\n\n
And let’s populate that table with a million rows (which I update after the insert to use an expression against the identity column):<\/p>\n\n\n\n
 INSERT dbo.Users(AboutMe)\n   SELECT name FROM\n   (\n     SELECT TOP (1000000) sc.name\n       FROM sys.all_columns AS sc \n       CROSS JOIN sys.all_objects AS so\n   ) AS x\n   ORDER BY NEWID();\n\n UPDATE dbo.Users SET \n   DisplayName = N'U' + RIGHT(CONCAT('000000',UserID),7),\n   Email       = CONCAT(LEFT(NEWID(),18),N'@gmail.com');<\/pre>\n\n\n\n
Spot checking the first few rows:<\/p>\n\n\n\n
 SELECT TOP (10) \n   UserID, DisplayName, Reputation, Email, AboutMe\n FROM dbo.Users;<\/pre>\n\n\n\n
\"Inspecting<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Let’s also say that the user interface that lets you page through these users, 25 at a time, lets you sort<\/em> by any of these columns – including AboutMe<\/code>. If you’re already getting hives, I don’t blame you.<\/p>\n\n\n\n
We’ll use dynamic SQL (a la the kitchen sink approach<\/a>, also due a refresh) for the initial paging mechanism, supporting a different plan for each sort option. I’m going to leave out error handling for brevity, and stick to the simple case where we’re just browsing all<\/em> users in some defined order (filtering will come later). We’ll skip right to the case where we use a CTE to first derive just<\/em> the key values before we pull the rest of the information, e.g.:<\/p>\n\n\n\n
 WITH keys AS \n (\n   SELECT UserID, \n          rn = ROW_NUMBER() OVER \n               (ORDER BY \/* something *\/)\n   FROM dbo.Users \n )\n SELECT u.\/* all relevant columns *\/\n   FROM dbo.Users AS u\n   INNER JOIN keys AS k\n      ON u.UserID = k.UserID\n   WHERE k.rn > ((@PageNumber - 1) * @PageSize)\n     AND k.rn <= (@PageNumber * @PageSize)\n   ORDER BY k.rn;<\/pre>\n\n\n\n
For the procedure in my test rig, I’m adding two additional parameters that you wouldn’t use in production: @TestNumber<\/code> for evaluating performance, and @Output<\/code> to suppress rendering results (I’ll dump the rows into a #temp table, so I can run lots of tests without overwhelming SSMS). I also use MAXDOP<\/code> to exaggerate the impact without parallelism. Here’s how the initial procedure looks:<\/p>\n\n\n\n
 CREATE OR ALTER PROCEDURE dbo.PagingAllUsers\n   @SortColumn   nvarchar(128) = N'UserID',\n   @SortOrder    char(4)       = 'ASC',\n   @PageNumber   int           = 1,\n   @PageSize     tinyint       = 25,\n   @TestNumber   int           = 1,\n   @Output       bit           = 0\n AS\n BEGIN\n   SET NOCOUNT ON;\n\n   DECLARE @sql nvarchar(max) = CONCAT(N'\n     DROP TABLE IF EXISTS #o;\n     WITH keys AS \n     (\n       \/* Test# ', @TestNumber, \n       ' - ', @SortColumn, ' - ', @SortOrder, \n       ' - ', @PageNumber, ' *\/',\n       N'SELECT UserID, rn = ROW_NUMBER() OVER \n         (ORDER BY ', QUOTENAME(@SortColumn), \n          N' ', @SortOrder, ') \n       FROM dbo.Users\n     )\n     SELECT u.UserID,\n            u.CreationDate,\n            u.DisplayName,\n            u.Reputation,\n            u.Email,\n            u.AboutMe',\n     CASE WHEN @Output = 0 THEN N' INTO #o ' END,\n     N' FROM dbo.Users AS u\n        INNER JOIN keys AS k\n           ON u.UserID = k.UserID\n        WHERE k.rn > ((@PageNumber - 1) * @PageSize)\n          AND k.rn <= (@PageNumber * @PageSize)\n        ORDER BY k.rn\n        OPTION (MAXDOP 1);');\n\n   EXEC sys.sp_executesql @sql, \n        N'@PageNumber int, @PageSize int',\n        @PageNumber, @PageSize;\n END<\/pre>\n\n\n\n
Now, this code and this table and this data may produce slightly different plans on your machine, with or without MAXDOP<\/code>, due to a variety of reasons. But the impact seen in the plans and runtime should be similar<\/em>.<\/p>\n\n\n\n
The good sort<\/h4>\n\n\n\n
This technique works great, of course, when we sort by the UserID<\/code> column (in either direction):<\/p>\n\n\n\n
 DECLARE @SortColumn sysname = N'UserID';\n \n EXEC dbo.PagingAllUsers @SortColumn = @SortColumn, \n                         @PageNumber = 1, @Output = 1;\n\n EXEC dbo.PagingAllUsers @SortColumn = @SortColumn, \n                         @PageNumber = 40000, @Output = 1;<\/pre>\n\n\n\n
\"Runtime<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
With a supporting index, even though it’s wide, no work is spent sorting. You can see how late in the plan the sort operator is introduced; after we’ve already filtered down to 25 rows:<\/p>\n\n\n\n
\"Plan<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
The bad sort<\/h4>\n\n\n\n
But let’s pretend there is a very good reason to sort by the AboutMe<\/code> column instead. When we put that in the ORDER BY<\/code>, it gets ugly:<\/p>\n\n\n\n
 DECLARE @SortColumn sysname = N'AboutMe';\n \n EXEC dbo.PagingAllUsers @SortColumn = @SortColumn, \n                         @PageNumber = 1, @Output = 1;\n\n EXEC dbo.PagingAllUsers @SortColumn = @SortColumn, \n                         @PageNumber = 40000, @Output = 1;<\/pre>\n\n\n\n
\"Runtime<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
The sort has to occur much earlier in order to locate the 25 rows we need:<\/p>\n\n\n\n
\"Plan<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
What can we do when we have to sort by some column that can’t be sorted efficiently?<\/p>\n\n\n\n
Saved by columnstore?<\/h4>\n\n\n\n
Often, a non-clustered columnstore index can compensate for the “I want to sort by any column but I don’t want to index every column” issue, as each column acts as its own little mini-index in a way. But it doesn’t help much in this case:<\/p>\n\n\n\n
 CREATE NONCLUSTERED COLUMNSTORE INDEX IX_NCCI \n   ON dbo.Users(UserId, DisplayName, CreationDate, Reputation, Email, AboutMe);\n<\/pre>\n\n\n\n
\"Runtime<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
The plans for both sort options are quite similar but, again, pay particular attention to where the sort happens (for UserID<\/code> it’s after we’re down to 25 rows; for AboutMe<\/code> it’s against all 1MM):<\/p>\n\n\n\n
\"Plan<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
While the sort may have been more efficient against fewer pages, it still has to sort all one million values – whether the 25 rows we’re after are at the beginning or end of the set.<\/p>\n\n\n\n
\"Plan<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
And while they both say<\/em> they represent 84%, they’re not<\/em> the same – those are relative (estimated) costs of two very differently-costed plans.<\/p>\n\n\n\n
Even in the best case scenario, where the columnstore index does help drive down duration, you still need to account for the overhead involved, and it might not always turn out in your favor<\/a>.<\/em><\/p>\n\n\n\n
Another technique<\/h4>\n\n\n\n
A way I have improved this in a few scenarios is to index a computed column representing the first n<\/code> characters of the longer string. Where to peg n<\/code> depends on the nature of the data, and has to allow for unexpected sort order if there are two rows where the first n<\/code> characters are identical. But let’s say we can be reasonably confident that the first 100 characters are “unique enough”:<\/p>\n\n\n\n
 ALTER TABLE dbo.Users \n   ADD AboutMePrefix AS (CONVERT(nvarchar(100), AboutMe));<\/pre>\n\n\n\n
 CREATE INDEX IX_AboutMe ON dbo.Users(AboutMePrefix);<\/pre>\n\n\n\n
In order to take advantage of this, our code needs to conditionally swap out the original column name with the computed column:<\/p>\n\n\n\n
 ...\n\n        N'SELECT UserID, rn = ROW_NUMBER() OVER \n          (ORDER BY ', QUOTENAME(CASE @SortColumn\n           WHEN N'AboutMe' THEN N'AboutMePrefix' \n           ELSE @SortColumn END,\n           N' ', @SortOrder, ') \n        FROM dbo.Users\n\n ...<\/pre>\n\n\n\n
Now we get performance much more in line with sorting based on any other narrow index:<\/p>\n\n\n\n
\"Runtime<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
\"Plan<\/a><\/figure>\n\n\n\n
<\/p>\n\n\n\n
You can play with the PERSISTED<\/code> option, but this may add unnecessary storage – it’s a common misconception that computed columns must be declared as PERSISTED<\/code> to be indexed, which isn’t true, though there are cases where persisting the column is important<\/a>.<\/p>\n\n\n\n
Neither choice avoids maintenance overhead, of course. Like any index or computed column, you need to weigh the benefit it provides against the maintenance cost it creates.<\/p>\n\n\n\n
At the end of the day, if people are changing their AboutMe<\/code> content a lot, or if there just aren’t that many people browsing users and<\/em> sorting by that column, then you may be better off just letting that specific paging operation continue to be slow.<\/p>\n\n\n\n
What if I can’t change the schema?<\/h4>\n\n\n\n
If you can’t change the structure of the Users<\/code> table, there are other ways to achieve a similar outcome. For example, you could have a separate table with just the AboutMePrefix<\/code> and UserID<\/code> columns, and maintain it manually (for real-time consistency, using triggers; or, if there can be a sync delay, through other scheduled means). When the sort is specified on that column, you dynamically swap in the new table instead of Users<\/code> in the original CTE, e.g.:<\/p>\n\n\n\n
 ...\n\n       FROM dbo.', CASE @SortColumn \n           WHEN N'AboutMe' THEN N'Users_AboutMePrefix' \n           ELSE N'Users' END, N'\n\n ...<\/pre>\n\n\n\n
You could also consider indexed views.<\/p>\n\n\n\n
Of course, everything in this post violates pure relational principles, but that’s a problem for another day…<\/p>\n\n\n\n
Conclusion<\/h3>\n\n\n\n
In a future post, I’ll talk about filtering, including trailing<\/em> wildcard searches against LOB columns – which can use the computed column if the pattern is short enough. Sometimes, though, pagination is just destined to hurt, and for advanced searching of large string columns, it might be time to look outside SQL Server (e.g. Elasticsearch<\/a>).<\/p>\n\n\n\n
\n    
FAQs: Pagination and ordering by large values<\/h2>\n\n                        
1. Why is SQL Server pagination slow when sorting by a large text column?<\/h3>\n            
\n                
OFFSET\/FETCH requires SQL Server to order the entire result set before it can skip to the requested page. When the ORDER BY column is a varchar(max) or nvarchar(max) column, SQL Server cannot use a standard B-tree index (LOB columns are not indexable directly), so it performs a full table scan and an expensive sort operation. As the table grows, pagination performance for large-column sorts deteriorates significantly.<\/p>\n            <\/div>\n                    
2. How do I fix slow pagination on a varchar(max) column in SQL Server?<\/h3>\n            
\n                
The most effective fix is to add a computed column that extracts the first N characters of the large column – e.g., CAST(LargeColumn AS nvarchar(200)) – and then index that computed column. Change your ORDER BY to sort by the computed column instead. This gives the query optimizer an indexable sort target. The N-character truncation is acceptable for pagination purposes because users rarely distinguish between page-sorted results that differ only in characters beyond position 200.<\/p>\n            <\/div>\n                    
3. Can a columnstore index fix large-column ORDER BY performance in SQL Server?<\/h3>\n            
\n                
In some workloads, yes. A non-clustered columnstore index can be used by the optimizer to avoid the full table scan component of a large-column sort, as columnstore indexes store data in compressed column segments that can be scanned more efficiently. However, columnstore is not a universal solution – it works best for analytical workloads and may not provide the same improvement as a computed column index for OLTP pagination scenarios.<\/p>\n            <\/div>\n                    
4. What if I cannot change the table schema to add a computed column?<\/h3>\n            
\n                
If schema changes are not possible, you can achieve a similar outcome using an indexed view that contains the computed column, or by maintaining a separate shadow table with the truncated column indexed separately. You can also use a covering index on a subset of important sortable columns to support at least the most common sort orders without modifying the base table.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
SQL Server OFFSET-FETCH pagination becomes extremely slow when sorting by large text columns (varchar(max), nvarchar(max)). Learn to fix it using computed columns, filtered indexes, and columnstore indexes with practical T-SQL examples.…<\/p>\n","protected":false},"author":341115,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[53,143531],"tags":[4151],"coauthors":[158980],"class_list":["post-101207","post","type-post","status-publish","format-standard","hentry","category-featured","category-t-sql-programming-sql-server","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=101207"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101207\/revisions"}],"predecessor-version":[{"id":109600,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101207\/revisions\/109600"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=101207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=101207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=101207"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=101207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}