Filtering, sorting and paging
Most screens show a filtered, sorted page of rows. This lesson covers WHERE with AND, OR, IN, BETWEEN, LIKE and NULL, ORDER BY with ties, and paging with OFFSET and FETCH, plus the keyset alternative that stays fast on page 5,000.
- StartFROMRead the table or index
- Step 2WHEREKeep matching rows; NULL checks need IS NULL
- Step 3ORDER BYSort, ending with a unique column
- Step 4OFFSETSkip earlier pages (or seek past the last key)
- EndFETCHReturn one page of rows
Filtering with WHERE
WHERE keeps only rows where the condition is true. Combine conditions with AND and OR, and use brackets so the meaning is clear.
SELECT ProductId, Name, Price
FROM Products
WHERE CategoryId IN (1, 4, 7)
AND Price BETWEEN 100 AND 500 -- includes both ends
AND (Name LIKE N'Pen%' OR Name LIKE N'%Notebook%')
AND DiscontinuedAt IS NULL; -- never "= NULL"NULL needs special care
NULL means unknown, so Price = NULL is never true, not even for rows where Price is NULL. Use IS NULL and IS NOT NULL.
NOT IN with a list that contains NULL returns no rows at all. If the list comes from a subquery that may hold NULLs, use NOT EXISTS instead.
LIKE '%text' with a leading wildcard cannot use an index seek; on large tables use full-text search or a starts-with pattern.
Sorting with ORDER BY
Without ORDER BY, SQL Server returns rows in whatever order is cheapest, and that order can change between runs. Always sort when the order matters.
Sort by several columns to break ties: ORDER BY Price DESC, ProductId. Adding a unique column last makes the order deterministic, which paging needs.
Paging with OFFSET and FETCH
OFFSET skips rows and FETCH takes the next batch. It needs an ORDER BY.
DECLARE @Page int = 3, @PageSize int = 20;
SELECT ProductId, Name, Price
FROM Products
ORDER BY Name, ProductId
OFFSET (@Page - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;Keyset paging for big tables
OFFSET still reads and throws away every skipped row, so page 5,000 is slow. Keyset (seek) paging remembers the last row of the previous page and starts after it, which is fast at any depth with an index on the sort columns.
-- @LastName and @LastId come from the last row of the previous page
SELECT TOP (20) ProductId, Name, Price
FROM Products
WHERE Name > @LastName
OR (Name = @LastName AND ProductId > @LastId)
ORDER BY Name, ProductId;Interview question
Why can OFFSET/FETCH paging get slow, and what is the alternative?
OFFSET n makes SQL Server produce and discard the first n rows of the sorted result before returning the page, so the work grows with the page number. Keyset (seek) paging filters with WHERE on the last seen sort key, such as Name > @LastName OR (Name = @LastName AND Id > @LastId), so an index can jump straight to the next page and the cost stays the same on every page.
Want the full details? Read the official Microsoft documentation for this topic.