Retrieving records within an index range, the nth record?

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?

Possible? How can I do it?

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?

I'm using SQL 2005 Express, not SQL 2000.if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?
Create index and schedule update statistics according to your requirement...

Use comparison operator for retrieving desired data.

i.e. Like, Between, Not Between etc.

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?
Use clustered and non clustered index considering your needs.

Indexes are for arranging, sorting & fast retrieval of the data. Each time you don't need to create index when you insert a row, just schedule update statistics job or set auto update statistics.

Explore Books OnLine (From query analyzer -> Help Menu) for more information.|||how do we include index as a criteria when we use normal SQL query?|||how do we include index as a criteria when we use normal SQL query?
You don't need to do such thing, SQL Server will do it for you...|||after some research, i think it's easier for me to insert row_number() into the table instead of using index. What do you think?|||Read documents / books (rather get some knowledge) before making any changes...|||Data in a relational database has no inherent order.
Why do you think you need to add a rownumber column?

There are several ways to get a "page" or "range" of records from a table. Here is one:

select top 5 *
from
(select top 10 *
from [YourTable]
order by [YourColumn] asc) Subquery
order by [YourColumn] desc

You should not be relying on the concept of a "row number"|||this is for a particular case to generate rigid report using SQL 2005.

I need the output to be in the right order in my control, and because there is headers and footers involved, I got no choice but to fix them in certain special order.

the output is to an excel spreadsheet.|||So throw an ORDER BY clause into your query.

If you want the data to be ordered according to the way it was entered, then use a datetime column to record the entry date.|||Order by cannot work without row_number. I have too many identical rows.

Entry date is not accurate as the time unit used by SQL 2005 is not small enough.|||Then use an Identity column.|||unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||You're not listening...|||You can also use temporary table to process desired request in memory, rather to store in table permanently,
try just like this:

SELECT ROWID=IDENTITY(int,1,1) , Col1
INTO #TempTable FROM
<UrTable List and Where Clause>

and then retrieve from Temporary table, it will save ur time, disk space and locking issues on underlying table.

--Riaz

unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||it will save ur time, disk space and locking issues on underlying table.
Dunno about time but this will use more disk space than blindman's query (temp tables are not held in memory but written to tempdb) and will lock tempdb while it runs (this is due to the "select into" bit).

You can use the OVER clause if you are really eager to use row_number().

SELECT *
FROM--Derived TABLE - numbering rows
(SELECT *
, ROW_NUMBER() OVER (ORDERBY my_unique_column ASC) AS rn
FROM dbo.MyTable) AS der_t
WHERE rn BETWEEN 5 AND 10