The clauses have no effect on non-correlated sub-select processing where the inner select qualifies many rows since they must all be processed before the outer select can be processed. The clauses do not avoid processing a work file if required for a hybrid join, materialization of a view, materialization of a nested table expression, etc. For ORDER BY, however, it does make it more likely that an index will be used, even one with a low cluster ratio, to avoid the sort if n is small (1 or 12 for example). OPTIMIZE FOR n ROWS and FETCH FIRST n ROWS ONLY have no impact on operations which require a sort, like ORDER BY, GROUP BY, DISTINCT, UNION, and merge join. Restrictions OPTIMIZE FOR n ROWS and FETCH FIRST n ROWS ONLY It is just an OPTIMIZER Directive to chose the optimized path and the limit of result set lies with the WHERE clause. But OPTIMIZE FOR n ROWS will not limit the result set. Note: FETCH FIRST n ROWS ONLY will limit the result set to вЂnвЂ™ rows. In a join query, the table with the columns in the ORDER BY clause is likely to be picked as the outer table if there is an index on that outer table that gives the ordering needed for the ORDER BY clause.Sequential prefetch is less likely to be requested by DB2 because it infers that you only want to see a small number of rows.List prefetch is less likely to be picked.This is because no sort would be needed for the ORDER BY. An index that matches the ORDER BY clause is more likely to be picked.Nested loop join is the most likely choice because it has low overhead cost and appears to be more efficient if you want to retrieve only one row. You can build the query as you go based on the user input.Example: The following statement uses that strategy to avoid a costly sort operation: SELECT LASTNAME,FIRSTNAME,EMPNO,SALARY Just put your whole query into the a variable. You can also use this approach to solve the problem from above with the dynamic WHERE clause. Keep in mind that SQL Server is providing no syntax checks of this statement until it actually runs so be very careful. You can use this to dynamically generate SQL statements at run time. Select = 'select top ' + convert(varchar, + ' * from MyTable Order by DateColumn'Д®xecute EXECUTE statement will run any valid SQL statement that you pass it. Easy, just make the whole SQL statement a variable. So how would you use the TOP in this case? Glad you asked. Micrsoft suggests using the TOP command whenever possible. There really isn't much more to it though. You can the SQL Server Books Online for further details on this command. Set rowcount * from MyTable Order by DateColumnДЄlways remember to use SET ROWCOUNT 0 to turn off the row limiter. In your case the syntax would look something like this: This statement stops processing after a certain number of rows have been processed. The easiest answer is to use the SET ROWCOUNT statement. Another question posted after yours asked why you couldn't put a whole WHERE clause in a variable ( Select * from table where = The SQL Server parser just won't let you. Well Ben, you've discovered a weird quirk of SQL Server: it's very picky about where it allows variables in queries.

Select top n in sql