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.
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:
- SQL SERVER – Turning Off Priority Boost Server Configuration Option on SSMS 18 Onwards
- SQL SERVER – Sample Script to Check Index Fragmentation with RowCount
- SQL Puzzle – Schema and Table Creation – Answer Without Running Code
- How to Know If Index is Forced on Query? – Interview Question of the Week #246
Reference: Pinal Dave (https://blog.sqlauthority.com)