I tried it in SQL Server 2000, but there was no reliable way to do this. I'm now wondering if SQL Server 2005 has any built in method?
What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on.
"You can use the Row_Number() function. Its used as follows:
SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName FROM Users
From which it will yield a result set with a RowID field which you can use to page between.
SELECT * FROM ( SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName FROM Users ) As RowResults WHERE RowID Between 5 AND 10
"If you're trying to get it in one statement (the total plus the paging). You might need to explore SQL Server support for the partition by clause (windowing functions in ANSI SQL terms). In Oracle the syntax is just like the example above using row_number(), but I have also added a partition by clause to get the total number of rows included with each row returned in the paging (total rows is 1,262):
SELECT rn, total_rows, x.OWNER, x.object_name, x.object_type FROM (SELECT COUNT (*) OVER (PARTITION BY owner) AS TOTAL_ROWS, ROW_NUMBER () OVER (ORDER BY 1) AS rn, uo.* FROM all_objects uo WHERE owner = 'CSEIS') x WHERE rn BETWEEN 6 AND 10
Note that I have where owner = 'CSEIS' and my partition by is on owner. So the results are:
"When I need to do paging, I typically use a temporary table as well. You can use an output parameter to return the total number of records. The case statements in the select allow you to sort the data on specific columns without needing to resort to dynamic SQL.
--Statements-- SELECT @Results = COUNT(ID) FROM Customers WHERE FirstName LIKE '%a%'
SET @StartIndex = @StartIndex - 1 --Either do this here or in code, but be consistent CREATE TABLE #Page(ROW INT IDENTITY(1,1) NOT NULL, id INT, sorting_1 SQL_VARIANT, sorting_2 SQL_VARIANT) INSERT INTO #Page(ID, sorting_1, sorting_2) SELECT TOP (@StartIndex + @PageSize) ID, CASE WHEN @SortColumn='FirstName' AND @SortDirection='ASC' THEN CAST(FirstName AS SQL_VARIANT) WHEN @SortColumn='LastName' AND @SortDirection='ASC' THEN CAST(LastName AS SQL_VARIANT) ELSE NULL END AS sort_1, CASE WHEN @SortColumn='FirstName' AND @SortDirection='DES' THEN CAST(FirstName AS SQL_VARIANT) WHEN @SortColumn='LastName' AND @SortDirection='DES' THEN CAST(LastName AS SQL_VARIANT) ELSE NULL END AS sort_2 FROM ( SELECT CustomerId AS ID, FirstName, LastName FROM Customers WHERE FirstName LIKE '%a%' ) C ORDER BY sort_1 ASC, sort_2 DESC, ID ASC;
SELECT ID, Customers.FirstName, Customers.LastName FROM #Page INNER JOIN Customers ON ID = Customers.CustomerId WHERE ROW > @StartIndex AND ROW <= (@StartIndex + @PageSize) ORDER BY ROW ASC