Learn / Oracle → SQL Server / Queries & Pagination
Lesson 2 of 5

Queries & Pagination

Core SELECT syntax is nearly identical between the two engines — joins, group by, subqueries all translate almost word for word. Where Oracle developers get tripped up is the handful of things Oracle needed a special mechanism for that SQL Server just... doesn't need, plus a few renamed string functions.

💡 The Bottom Line

Oracle's ROWNUM pseudo-column and the one-row DUAL table both exist to work around limitations that SQL Server simply doesn't have — SQL Server can run a SELECT with no FROM clause at all, and has a purpose-built OFFSET...FETCH clause for pagination. Where Oracle mostly renamed C-style string functions (SUBSTR, INSTR), SQL Server mostly kept SQL-standard names (SUBSTRING, CHARINDEX) — the logic is the same, only the spelling differs.

Pagination: ROWNUM vs OFFSET/FETCH

Oracle
SELECT *
FROM (
  SELECT e.*, ROWNUM rn
  FROM (
    SELECT * FROM employees
    ORDER BY hire_date DESC
  ) e
  WHERE ROWNUM <= 20
)
WHERE rn > 10;
SQL Server
SELECT *
FROM employees
ORDER BY hire_date DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

📋 Key differences

  • Oracle's ROWNUM is assigned before ORDER BY is applied to the outer query, which is why the classic pattern needs a nested subquery just to paginate correctly — a very common Oracle interview gotcha in its own right.
  • SQL Server's OFFSET...FETCH (added in SQL Server 2012) does exactly what it reads as: skip N rows, then take the next M — no subquery gymnastics required, and it requires an ORDER BY to be meaningful.
  • Oracle 12c+ also added a direct equivalent, OFFSET n ROWS FETCH NEXT m ROWS ONLY — if you're on a modern Oracle version, that syntax is now closer to SQL Server's than the classic ROWNUM pattern above.
  • For "just give me the top N," Oracle's modern FETCH FIRST n ROWS ONLY maps to SQL Server's SELECT TOP (n).

The DUAL table vs no table at all

Oracle
SELECT SYSDATE FROM DUAL;

SELECT 1 + 1 FROM DUAL;
SQL Server
SELECT GETDATE();

SELECT 1 + 1;

Oracle's grammar requires every SELECT to have a FROM clause, so Oracle ships a permanent one-row, one-column table called DUAL purely so you can select a constant or call a function without a real table. SQL Server's grammar has no such restriction — FROM is optional, so you just omit it.

⚠️ Gotcha

If you copy-paste an Oracle query into SQL Server and it still says FROM DUAL, it will actually fail — SQL Server has no built-in DUAL table (some teams create a fake one for compatibility, but it's not there by default). Just delete FROM DUAL entirely; don't try to find its SQL Server equivalent, because there isn't one — it isn't needed.

String functions: renamed, not reinvented

Oracle
SELECT SUBSTR(last_name, 1, 3),
       INSTR(last_name, 'a'),
       LENGTH(last_name)
FROM employees;
SQL Server
SELECT SUBSTRING(last_name, 1, 3),
       CHARINDEX('a', last_name),
       LEN(last_name)
FROM employees;

📋 Key differences

  • SUBSTR(string, start, length) → SUBSTRING(string, start, length) — same three arguments, same order, just the SQL-standard spelling.
  • INSTR(string, search) → CHARINDEX(search, string) — note the argument order flips: Oracle takes (haystack, needle), SQL Server takes (needle, haystack).
  • LENGTH(string) → LEN(string) — same behavior, shorter name. One subtlety: SQL Server's LEN() trims trailing spaces from the count; Oracle's LENGTH() does not.
  • Concatenation inside these calls still follows lesson 1's rule: || in Oracle, + or CONCAT() in SQL Server.

🔧 Try It Yourself

Take an Oracle report query that uses ROWNUM for a "top 20" list and rewrite it as a SQL Server SELECT TOP (20) ... ORDER BY .... Then take a second query that pages results (rows 11–20) and rewrite it using OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY. Notice how much shorter the SQL Server version is — no nested subquery required.

🙋 There Are No Dumb Questions

Q: Does SQL Server have anything like ROWNUM I should know about?
A: The closest analog is ROW_NUMBER() OVER (ORDER BY ...), a window function that assigns a sequential number per row based on an explicit ordering — and unlike classic Oracle ROWNUM, it's applied to already-ordered results, so it doesn't have the "filter-before-sort" trap that makes the classic ROWNUM pagination pattern necessary in the first place. Oracle also has ROW_NUMBER() as an equivalent window function, if you'd rather use the same construct on both sides.

✅ Quick Check

You want to select the literal value 42 with no table involved. In SQL Server, what's the correct query?

Next up: how each engine auto-generates numbers — Oracle's standalone SEQUENCE objects versus SQL Server's IDENTITY columns.

← Back to
Next →