1. RETURN_TOP N
This hint limits the number of rows that a query returns. If you are not interested in seeing all the rows but only the top five (for example), use this code:
SELECT user_name FROM dm_user ENABLE (RETURN_TOP 5)
2. OPTIMIZE_TOP N
This hint directs the database server to return the first n rows a query returns quickly. The remaining rows are returned at the normal speed.
SELECT r_object_id FROM dm_sysobject ENABLE (OPTIMIZE_TOP 4)
3. FORCE_ORDER
The FORCE_ORDER hint controls the order in which the tables referenced in the query’s FROM clause are joined. The tables may be relational database management system tables or object-type tables.
SELECT object_name FROM dm_sysobject ENABLE (FORCE_ORDER)
