Duncan wrote:
Hi,
The solution I use for paging which seems to work quite well for reasonable size result sets is to use a sub query, it has the advantages of not requiring a temp table or reading all data at once. It would go on this general idea:
SELECT TOP 10 ProductID, Name FROM Product
WHERE CategoryID = 3 AND ProductID NOT IN
(SELECT TOP 20 ProductID FROM Product
WHERE CategoryID = 3
ORDER BY Name)
ORDER BY Name
The outer TOP defines the page size, the inner TOP is the page size * the page.
The following must be true for this to work, with the exception of the NOT IN WHERE clauses must be identical, ORDER BY clause must be identical for the inner and outer queries.
Neat , however this requires sql construction for the inner top clause, plus I think it will be not that efficient, due to the double full table scan for the top clause in combination of the order by. However, it's clever found I didn't think of this. With SqlServer 2000 you can also use a user defined function which returns a table variable with the rows, indexed using a new column which is an identity column (and the only key). That way, you don't rely on the tempdb but will create the table completely in memory, the same way as the subquery does. That solution will also be generic, since you can then pass a parameter to filter on the identity column in the table variable returned from the user defined function, where to start the current page (and a parameter for how many rows of course ).
Scott: Predicates don't use subqueries, so you can't construct this query with predicates.
You can however do this. This is an example which targets Northwind. Use Query Analyzer to quickly get the column definitions (right mouse on table, select 'SCript object to new window as Create). It adds a new column, Index, as Identity, and doesn't define a key, because we'll select all rows anyway.
CREATE FUNCTION fnCustomersIndexer ()
RETURNS @indexedCustomers TABLE (
[Index] [int] IDENTITY (1, 1) NOT NULL,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
AS
BEGIN
-- insert all customers from table into this table variable
INSERT @indexedCustomers
SELECT * FROM Customers
-- done
RETURN
END
This function creates an in-memory table object with a new column, 'Index'. You can now filter on this index, using:
SELECT * FROM fnCustomersIndexer()
WHERE [Index] > 10 AND [Index] < 20
Or you can create a view:
CREATE VIEW vwTest
AS
SELECT * FROM fnCustomersIndexer()
Which you then can use with predicates filters!