![]() ![]() ![]() |
Ispirer SQLWays Database Migration Software
Conversion of Microsoft SQL Server TOP clause to Oracle
The Microsoft SQL Server TOP clause limits the number of rows returned by a SELECT statement. TOP allows specifying the number or percentage of rows to return. If the SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set.
The Oracle ROWNUM pseudocolumn returns a number indicating the order in which the row was selected in the result set. ROWNUM can be used to limit rows in Oracle, but ROWNUM is assigned before ordering.
SQLWays converts the Microsoft SQL Server TOP clause to Oracle as follows:
a)SELECT statement with TOP does not contain the ORDER BY clause.
If Microsoft SQL Server does not contain the ORDER BY clause, SQLWays implements the TOP clause using the ROWNUM pseudo column in the WHERE clause of the SELECT statement in Oracle.
If TOP is specified with the PERCENT clause SQLWays calculates the total number of rows retuned by the query and the number of rows corresponding to the specified percentage.
TABLE 36. SELECT statement with TOP does not contain the ORDER BY clause Microsoft SQL Server Oracle
b) SELECT statement with TOP also contains the ORDER BY clause.
Unlike Microsoft SQL Server, Oracle applies comparison with ROWNUM before ordering the result set. If the Microsoft SQL Server SELECT statement contains the ORDER BY clause, SQLWays converts the source query to the query with the subquery. The subquery performs the ordering, while the query performs row restricting using ROWNUM.
![]() ![]() ![]() |