Paging DataGrid :X rows from Y position

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Paging DataGrid :X rows from Y position

Author

Topic

RBC
Starting Member

17 Posts

Posted - 2009-01-03 : 18:00:13

Hi,

What will be the inline SQL to select 10 rows with a start value.
Issue (1) is that I have 100 rows and I like to pick 10 rows by paging: << <> >>
Issue (2) is that the 100 rows can be sorted, so I need to select the 10 rows from the right sort order (Index).

Can we select rows by using the index of the table in the select statement?

Please see the SP code below which works on ProductID; because the table Products is ASC on ProductID.

Issue (3) is that if PK is not IDENTENTY; the code below doesn't work. My PK is nvarchar...of course.

What I am asking for is as follow: SP or inline SQL (or LINQ sample:) with possibility to select 10, 20, or 30 rows by different sort orders with a start position in the table.

Northwind Products table has ProductID as ASC and it has a index on ProductName.

So I like to select X rows from Products with start position Y sorted on ProductID or ProductName.

The code below select X rows from Products with start position Y sorted on ProductID.

I assume I can sort the table on ProductName and then select as the code below. Why not select the data from ProductName index when it is already indexed ProductName...This is possible on IBM AS/400)

Thank you,
Rune

CREATE PROCEDURE [dbo].[usp_PageResults_Products]

(
@startRowIndex int,
@maximumRows int,
)

AS

DECLARE @first_id int
SET ROWCOUNT @startRowIndex
SELECT @first_id = ProductID
FROM Products
where Discontinued = 0
ORDER BY ProductID

SET ROWCOUNT @maximumRows
SELECT ProductID,ProductName,Discontinued from Products
WHERE Discontinued = 0 and ProductID >= @first_id
ORDER BY ProductID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-03 : 19:26:01

Darn!

I only have the code to get n records from position p.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-03 : 19:26:49

Seriously... You have previosuly been suggested to search this site for paging algorithms.
There are lot of them.

E 12°55'05.63"
N 56°04'39.26"

RBC
Starting Member

17 Posts

Posted - 2009-01-03 : 23:16:28

Peso,

What I am looking for is a select statement using the index of the table.

SELECT ProductID, ProductName
FROM Northwind.Products
BY INDEX ProductName

Is it possible?

Thank you,
Rune

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-04 : 04:30:29

Yes.
See Books Online for query hints.

What you want is

WITH (INDEX (ix_ProductName))

after the table name in the query.

E 12°55'05.63"
N 56°04'39.26"

RBC
Starting Member

17 Posts

Posted - 2009-01-04 : 08:00:10

Which is your favorite book online?
Please send me the URL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 02:28:37

Books Online is the SQL Server help file that is installed together with your client tools.

E 12°55'05.63"
N 56°04'39.26"

RBC
Starting Member

17 Posts

Posted - 2009-01-05 : 09:48:31

OK,

...but I do not find any WITH (INDEX (ix_ProductName)) samples?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 09:56:28

WITH INDEX is a query hint.

E 12°55'05.63"
N 56°04'39.26"

RBC
Starting Member

17 Posts

Posted - 2009-01-05 : 10:29:00

I thougt this was a code snippet web site and not a book club:)

Thank you for putting me on right track. Here is the Index hint sample and URL you use in the SQL 2005 Help URL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9b9caa3d-e7d0-42e1-b60b-a5572142186c.htm

The example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table.

-- Use the specifically named INDEX.
USE AdventureWorks ;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee e WITH (INDEX(IX_Employee_ManagerID))
JOIN Person.Contact c on e.ContactID = c.ContactID
WHERE ManagerID = 3 ;
GO

-- Force a table scan by using INDEX = 0.
USE AdventureWorks ;
GO
SELECT c.LastName, c.FirstName, e.Title
FROM HumanResources.Employee e WITH (INDEX = 0) JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE LastName = 'Johnson' ;
GO

RBC
Starting Member

17 Posts

Posted - 2009-01-05 : 10:54:44

Sample 2 Force a table scan by using INDEX = 0.
What is INDEX 0?
How do I find index 0, 1, 2, etc?
I assume if I build 3 index, they will have number 0, 1, and 2; correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources