Limit record like ROWNUM in MS SQL server 2000

I want to know how to limit records in SQL Server 2000. In Oracle, I already wrote a SQL query like that.

<cfquery name="myQuery" datasource="myDSN">
    SELECT * from tbl where rownum &开发者_StackOverflowlt;= 10
</cfquery>

In SQL Server 2000, I cannot limit any records within a SQL query. I know I can use "maxrows" of cfquery but I don't want CF to crawl the whole table first and limit it after all.

For SQL Server:

SELECT TOP(10) * 
  FROM tbl

For MySQL (also PostgreSQL, SQLite):

SELECT * 
 FROM tbl
LIMIT 10

IN SQL Server you can use the Set Rowcount command to limit the number of rows returned

   SET ROWCOUNT 10
   SELECT * FROM TBL

Just remember to turn it back off afterwards by using

SET ROWCOUNT 0