0

Is it possible to do a key set pagination for nvarchar(max) column in sql server I understand that its possible for int, dates as we can check < value or > value

Suppose I have a table

Someid Name
1 Abc
2 Cde

Currently its written something like this

SELECT * FROM table
WHERE 'somelogic'
Order by Name Asc Offset 1 fetch 1

The problem is its currently ordered by Name because thats the requirement In page number 100th its taking a lot of time to fetch the result

Edit: Currently there is no condition present on filtering the result based on Name (where Name='somename' -- this is not present) only its being orderd by Name Asc. This is offset pagination if I am not wrong. I want to know if I can do a Keyset pagination for that.

Abhishek Bagchi
  • 23
  • 6
  • *Any* data type can be ordered, yes. `'Cde'` is *greater than* `'Abc'` because `'C'` is *after* `'A'`. The bigger problem is that you want to pass a `nvarchar(MAX)` value as your paramter, which means you are saying you want to pass a value that is likely to be *over 4000 characters* in length; you aren't going to have an `INDEX` to help you there. – Thom A Dec 15 '22 at 14:02
  • How would I offset that? – Abhishek Bagchi Dec 15 '22 at 14:04
  • [Did you try it](https://dbfiddle.uk/jJZgBczM)? The other problem aside from horrible sort performance is going to come down to ties, how do you break a tie and ensure consistency? Anyway for the performance aspect you might consider a computed column of the first few characters, and index that, then at least you can narrow down to the page you want pretty efficiently. – Aaron Bertrand Dec 15 '22 at 14:08
  • `>` would still work, @AbhishekBagchi . `WHERE YourMAXLengthColumn > @YourMAXLengthParameter` The problem, as Aaron has also now mentioned, is that due to the value(s) being over 4,000 characters they will be far too large to fit in an index, which means that the sorting will be horribly expensive and your query will likely be far from performant. – Thom A Dec 15 '22 at 14:11
  • Sounds like a weird design, why would you want to fetch data in some text order? You could create a computed PERSISTED column which is CAST(LEFT(Name,300) AS NVARCHAR(300) and create index on that, then change your sort thingy to Order by NameComputed, Name Offset 1 fetch 1 If you're lucky, it might work better. Or just bite the bullet and don't fetch stuff in weird orders / paging – siggemannen Dec 15 '22 at 15:10
  • Did you choose `nvarchar(MAX)` because you actually have such large values? If the actual number of characters is 850 or less, you can use `nvarchar(850)` and create a non-clustered index on the column to improve pagination performance. – Dan Guzman Dec 15 '22 at 15:23
  • @siggemannen You don't have to persist it to index it (persisting it only stores it an additional time). – Aaron Bertrand Dec 19 '22 at 12:14
  • @AaronBertrand i didn't know that! We always persisted our indexed comp columns, cause it always gave some errors otherwise, but now when i read the docs, it should work as long as column formulas are "precise". Good call! – siggemannen Dec 20 '22 at 17:59

0 Answers0