SQL for .NET developers Beginner

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.

How a paged query runs
  1. StartFROMRead the table or index
  2. Step 2WHEREKeep matching rows; NULL checks need IS NULL
  3. Step 3ORDER BYSort, ending with a unique column
  4. Step 4OFFSETSkip earlier pages (or seek past the last key)
  5. 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.

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

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

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

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.