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

October 20, 2019

Question: Retrieve TOP 10 Rows Without Using TOP or LIMIT?

Answer: When I was asked this question in my recent Comprehensive Database Performance Health Check, I asked back to the person why he wants to re-invent the wheel. However, when I received his answer I actually now truly agree with the reasoning.

Retrieve TOP 10 Rows Without Using TOP or LIMIT? - Interview Question of the Week #247 retrieve-top-800x229

Here is the reason: We do not want to use TOP or LIMIT as they are actually not an ANSI implementation. We want to retrieve the top rows in such a way that it can work across different databases like SQL Server, MySQL, MariaDB as well as PostgreSQL.

All the databases have implemented their own logic to retrieve the top rows. SQL Server uses TOP keywords whereas MySQL, MariaDB, and PostgreSQL uses the LIMIT keyword to restrict the rows in the SQL Server. This presents unique challenges to third party vendors who are writing code which can be plugged into any RDBMS. They have to write code witch conditions and maintain two different versions of the logic. However, if you use the following method it will work in all the recent versions of the relational databases.

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

This will work with all the known relational databases I have come across. While we love to use TOP and LIMIT often it is recommended that we keep on using the logic which works across multiple relational databases.

Here are other blog posts which you may find interesting:

Reference: Pinal Dave (https://blog.sqlauthority.com)

MariaDB, MySQL, PostgreSQL, SQL Server

How to Know If Index is Forced on Query? – Interview Question of the Week #246

Why Query Store Actual Operation Mode is Not Same as Requested? – Interview Question of the Week #248

3 Comments. Leave new

Wilfred van Dijk
October 20, 2019 4:09 pm
Hi Pinal,
Although I think it’s a good idea to confirm to a certain standard, your solution gives a different and worse execution plan in SQL.
I tested it with an enlarged AdventureWorks2016 database (SQL2017 developer) using a select from view vSalesPersonSalesByFiscalYears. The first run was done using TOP(100) and the 2nd run was using the ROW_NUMBER() solution.
For this relatively small result set the cost for using TOP was 2,32761 and for the ROW_NUMBER() it was 2,68877. This is an increase by 15%. I also tested it on one of our large acceptance databases (SQL2012 enterprise) and the result was almost 20% slower using ROW_NUMBER()
So you pay a price if you want to stick to a ‘generic’ query. I prefer a maximum performance.
Reply
- Pinal Dave
  October 20, 2019 5:38 pm
  Hi Wilfred,
  Thanks for the test. Yes that would happen.
  Just so you know this post was not about tuning the query but rather wanted a query that would work some of the major databases. Additionally, the TOP has different breakpoint when it is about performance, I will write a blog post about it in the future.
  Reply
Sushanth
October 22, 2019 6:13 am
Materialization of whole resultset and then filtering will be a problem. For a interview this might be ok.
Reply