I wanted to do some, for me, unusual SELECT statements on data that we had for one of our customers; I wanted to select data between two sequential values, but unfortunately, none of the columns assisted me in anyway (they have a lot of varchar columns).  So I remember that with SQL Server 2005, Microsoft provided some sort of row ID.  So after a bit of digging around (in other words Googling!), I found Microsoft provide the function ROW_NUMBER().  Ha ha, just the ticket!  So after 10 minutes of playing with the new function, here is what I came up with:

WITH AS

(

SELECT ROW_NUMBER() OVER (ORDER BY ASC) AS 'ROWID',
* FROM

)

select * From where ROWID between 0 and 10

The isn't the normal #temp table, because if you do a select from it, the table doesn't exist, only within the WITH clause.
I hope this helps others as it took me time to find the answer and if anyone knows what the WITH clause means, I would be interested to hear.