Lesson 2 of 5

Queries & Pagination

The tables now exist in both worlds — the next friction point is shaping a result set. Limiting row counts, paging through results, and handling NULLs all use different keywords in SQL Server than the ones muscle memory expects from MySQL.

💡 The Bottom Line

LIMIT n becomes TOP (n), and it moves from the end of the query to right after SELECT. The short LIMIT n OFFSET m becomes the far more verbose OFFSET m ROWS FETCH NEXT n ROWS ONLY — and that clause only works alongside an ORDER BY, which T-SQL requires for it. IFNULL(a, b) becomes ISNULL(a, b) (or the standard COALESCE, which both engines support). Everything else about SELECT/WHERE/ORDER BY reads almost identically.

Limiting rows: LIMIT vs TOP

MySQL (what you know)

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

SQL Server (the new equivalent)

SELECT TOP (10) *
FROM Orders
ORDER BY OrderDate DESC;

📋 Key differences

LIMIT sits at the very end of the query, after ORDER BY; TOP sits right after SELECT, before the column list — a much bigger jump in position than the name change alone suggests.
SQL Server's TOP without parentheses (SELECT TOP 10 *) is legal legacy syntax, but the parenthesized form TOP (10) is the modern, recommended one — MySQL's LIMIT has no equivalent bare-vs-parenthesized distinction, it's always LIMIT n.
TOP can take a PERCENT modifier (TOP 10 PERCENT), and even a WITH TIES modifier that includes extra rows tied with the last one returned by the ORDER BY; MySQL's LIMIT has no percent or ties form — you'd compute the row count or handle ties yourself.

Paging: LIMIT-OFFSET vs OFFSET-FETCH

MySQL (what you know)

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 20;

SQL Server (the new equivalent)

SELECT *
FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Both statements return the same thing: skip the first 20 rows, return the next 10. MySQL's LIMIT count OFFSET offset is short and to the point (MySQL also accepts a comma form, LIMIT offset, count, which reverses the argument order and is a common source of off-by-swap bugs even for MySQL veterans). SQL Server's ANSI-standard OFFSET ... FETCH NEXT ... ROWS ONLY is far more verbose, but explicit about which number is the skip and which is the page size — and, critically, it requires an ORDER BY clause to be present at all; MySQL's LIMIT works with no ordering whatsoever (though the result order is then technically undefined).

⚠️ Gotcha

Trying to write OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY on a query with no ORDER BY throws a compile error in SQL Server — ORDER BY is mandatory for OFFSET/FETCH, unlike MySQL where LIMIT happily runs (and picks some arbitrary, storage-order-dependent set of rows) with no ORDER BY at all. If you're porting a MySQL query that relied on an implicit "good enough" order without ever writing one, SQL Server will force you to add an explicit ORDER BY before it lets the query run.

NULL handling: IFNULL / COALESCE vs ISNULL

MySQL (what you know)

SELECT IFNULL(middle_name, '') AS middle_name
FROM users;

SQL Server (the new equivalent)

SELECT ISNULL(MiddleName, '') AS MiddleName
FROM Users;

📋 Key differences

IFNULL(a, b) is MySQL's two-argument NULL substitution function; SQL Server's direct equivalent is the similarly-shaped, non-standard ISNULL(a, b) — note it's a completely different function from the ANSI NULLIF, despite the similar name.
Both engines support the ANSI-standard COALESCE(a, b, c, ...), which takes any number of arguments and returns the first non-NULL one — it's the more portable choice if you expect to support more than one database going forward.
Watch return-type behavior: SQL Server's ISNULL takes its result type from the first argument, which can cause silent truncation if the fallback value is longer than the first argument's declared type — a subtlety MySQL's more loosely-typed IFNULL doesn't share. COALESCE on both engines determines the type from all arguments, which is usually safer.

String functions: SUBSTRING differences

MySQL (what you know)

SELECT SUBSTRING(email, 1, 5) FROM users;
-- also: SUBSTR(email, 1, 5)
-- also: SUBSTRING(email, -5) for the last 5 chars

SQL Server (the new equivalent)

SELECT SUBSTRING(Email, 1, 5) FROM Users;
-- no negative-start shortcut —
-- combine RIGHT() and LEN() instead

SUBSTRING(string, start, length) works the same way on both engines — 1-based indexing, three arguments. The difference shows up at the edges: MySQL's convenience of a negative start position doesn't carry over. SQL Server's SUBSTRING requires a positive starting position and a length, always — for "last 5 characters" you'd write RIGHT(Email, 5) instead of relying on SUBSTRING(Email, -5) the way you could in MySQL.

🔧 Try It Yourself

Take a MySQL paging query that uses LIMIT count OFFSET offset and rewrite it for SQL Server using OFFSET offset ROWS FETCH NEXT count ROWS ONLY — remember to add an explicit ORDER BY if your original query didn't have one, since T-SQL won't run without it.

🙋 There Are No Dumb Questions

Q: Does SQL Server support LIMIT at all, so I don't have to change my queries?
A: No — SQL Server only understands TOP and the ANSI OFFSET ... FETCH NEXT ... ROWS ONLY form. MySQL's own LIMIT/ LIMIT ... OFFSET syntax is not valid T-SQL and will throw a syntax error — this is one of the few places SQL Server chose the more verbose standard phrasing over MySQL's shorthand.

✅ Quick Check

A MySQL developer writes SELECT * FROM Orders OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; against SQL Server, with no ORDER BY clause. What happens?

Next up: once rows are flowing, the next question is how each engine assigns and retrieves auto-generated key values for new rows — AUTO_INCREMENT and LAST_INSERT_ID() have SQL Server equivalents that behave a little differently.

← Back to