Retrieve TOP 10 Rows Without Using TOP or LIMIT? – Interview Question of the Week #247

Question: How do you retrieve ten rows without TOP or LIMIT? Number the rows with ROW_NUMBER, then filter the numbered result in an outer query.

When a health-check client asked this, my first reaction was, “Why reinvent the wheel?” Their reason was sensible: they were writing software for more than one database engine, and TOP and LIMIT are different dialect choices.

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
FROM (
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION,
           ROW_NUMBER() OVER (ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME) AS ROWNUM
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'PROCEDURE'
) AS T
WHERE ROWNUM <= 10
ORDER BY ROWNUM;

This keeps the original example, listing the first ten stored procedures from INFORMATION_SCHEMA.ROUTINES. I added the schema to the ordering so same-named procedures in different schemas don’t tie. The final ORDER BY also matters: the window’s ordering assigns numbers but doesn’t guarantee the displayed order.

Ten rows means at most ten. If fewer procedures are visible, fewer rows are returned. These are the first names in the chosen ordering, not the ten most important or most frequently executed procedures.

The row-number pattern is broadly useful across modern database engines, but the original claim that this exact query works on every recent database was too broad. Window-function availability depends on version, and catalog columns and permissions differ. For example, SQL Server’s ROUTINE_DEFINITION is limited to 4,000 characters and is not a complete procedure-export tool.

Use this pattern when its portability helps your application. It isn’t automatically faster than TOP, and a different engine still needs its own verification. The interview lesson is the outer filter: a window result is calculated too late to use its alias in the same SELECT’s WHERE clause.

Related: recognizing a forced index and fragmentation with row counts.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.