"LIMIT" in SQL Server

I've recently come across a number of folks in different contexts who were trying to figure out how to acheive the equivalent of MySQL's "LIMIT" clause in SQL Server.  The basic scenario is that you want to return a subset of the results in a query from row number X to row number Y.

The good news is that SQL Server 2005 makes this really easy.  We introduced a new set of ranking functions into the T-SQL language that let you accomplish the basic LIMIT semantics and much more if you feel like getting fancy.

Quick example:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10

The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column.  ROW_NUMBER() is the key function we're using here.  It's one of a set of ranking functions introduced in 2005.  Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on. 

For details on the ROW_NUMBER() function and its use, see: http://msdn2.microsoft.com/en-us/library/ms186734.aspx

For info on other ranking functions in SQL Server 2005, see: http://msdn2.microsoft.com/en-us/library/ms189798.aspx