How to retrieve rows 50-60 (or anything else)

Let's say that we have a table and we want to retrieve only rows 50 to 60 from it. We can accomplish that in more than one way. In this short blog I will show the shortest (in my mind anyway) way to do that.

Our table scheme looks like this

CREATE TABLE testTable ( 
   id INT, 
   title NVARCHAR(250), 
   data XML
) 

In order to obtain the required rows we will use the CTE, which is built into the SQL Server.

WITH t1 (rowId, id, title, data) AS ( 
   SELECT ROW_NUMBER() OVER (ORDER BY id), id, title, data 
   FROM testTable
) 
SELECT * FROM t1 
WHERE rowId BETWEEN 50 AND 60

As can be seen in this example, the rows 50 to 60 are display as the result of the query.
Now I will explain what was done in this sample.
1. A temp table named t1 was created.
2. A query in the form similar to SELECT * FROM testTable was inserted into the new temp table. In addition to all of the fields, a new function named ROW_NUMBER() was used. This function adds (similar to IDENTITY) incremented row numbers.
3. Finally, a simple select between statement was called.

Hope that this is in some help to all.... I know that it's good and simple way to retrieve data.