Paging Support in SQL Server 2005 (Finally)
Retrieving data from database is not such a trivial task when data grew in size. Imagine taking thousands of row when you only need 10 at a time. MySQL has (for a long time now) a very elegant solution in their SQL implementation, LIMIT and OFFSET.
SQL Server 2005 has this new thing called CTE (Common Table Expression) that substitutes subquery and ROW_NUMBER() function, both can be used to create a query that returns pages (or groups) of rows. It's less elegant, but have more power.
Here's the query:
WITH OrderedSkills AS (SELECT Id, [Name], ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Skill)
SELECT Id, [Name] FROM OrderedSkills WHERE RowNumber BETWEEN @StartIndex AND @EndIndex;
Line 1 creates a temporary table (named OrderedTable) with two columns, one is Id taken from table Table, the other one is row number. Line 2 queries OrderedTable returning single column Id, limited on specific RowNumber column.