Difference Between Top and Limit Keyword in SQL

Difference between Top and Limit Keyword in SQL

If you are using SQL Server use TOP if you are using MySQL or Postgres use Limit!

AFAIK there is no product that currently supports both. Here's one list of current implementations and here's another (covers more products but in less detail)

Difference between MySQL's LIMIT and Teradata's TOP when conditioning on non-indexed variables

First, you should not use LIMIT / TOP unless you also use ORDER BY, if you are interested in what exactly is returned (other than the fact there are so many records at all).

Having said that, MySQL will not scan the whole table (if tab is a table) or index prior to returning the LIMIT records: it will return the records as it scans and filters them, and will stop as soon as there are enough of them.

However, if tab is not a table, but a view, and if it contains a nested query, or a GROUP BY condition or something like that, MySQL might need to scan all the underlying tables prior to filtering and limiting indeed.

MAX vs Top 1 - which is better?

Performance is generally similar, if your table is indexed.

Worth considering though: Top usually only makes sense if you're ordering your results (otherwise, top of what?)

Ordering a result requires more processing.

Min doesn't always require ordering. (Just depends, but often you don't need order by or group by, etc.)

In your two examples, I'd expect speed / x-plan to be very similar. You can always turn to your stats to make sure, but I doubt the difference would be significant.

How universal is the LIMIT statement in SQL?

http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows lists all of the major variants of the select command.

I believe the best way to do this is to use the SET ROWCOUNT command before your SELECT statement.

So, for you:

SET ROWCOUNT 1
SELECT %s FROM %s

SQL Server TOP keyword returns different results

To add some context in this question, what I was trying to achieve is consistency between MySQL and MSSQL in the ordering of rows returned. I am converting a project that used MySQL over to MSSQL and this produced a problem for me. The solution to get what I want was to use Set Rowcount 1 to limit the number of rows returned. This produced 1 row of data which was ordered the same way as MySQL does.

Equivalent of LIMIT and OFFSET for SQL Server?

The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE 
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).

Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.

---

Related Topics