Laurenz Albe digs into keyset pagination:
Keyset pagination is the most performant way to retrieve a large result set page by page. However, the neat trick with composite type comparison doesn’t always work. This article explains why and how you can work around that shortcoming.
All of the examples are for Postgres, though in my read-through of the post, I’d say it applies almost as well to SQL Server.