how to select multiple rows?

add a field to the table which has an identity seed on it
then select where this field is between 100 and 300

Since I strongly suspect that adding an ID will not do it for you (too many deletes), you have currently two options:
1.  Load a temporary table with an ID column.
2.  If (and this is a big if) you have an appropriate primary key use SELECT TOP to exclude the unwanted rows.  Warning this can be slow.

what Karinlos said is true..

but a an extra.. not a good idea but u can use

Select top 100% t1.from
   ( select top 300 * from table_1 ) t1 left join
   ( select top 100 * from table_1 ) t2 on t1.PK = t2.Pk
  where t2.column1 is null

or

select top 200 *
from
(select top 300 *
     from table
     Order by YourOrderColumn asc
) b
Order by YourOrderColumn desc

there is no way to create a new column to index rows.i already have a primary key, but the rows aren't sorted. is there any way to sort these rows without creating a new table? How can I sort the table, not when I'm running the query because this takes too long?

intresting simon, but it takes to much time.

If you are trying to select rows 101 to 300 (because you previsously selected top 100).
and you are ordering by ID for example:

Remember the ID from row 100 of the first select (call id IDLast) then ...

select top 200 * from table
where id > idlast
order by id

i cant order the table when i submit the query. it takes to much time. :(

when you add a field with an identity seed it does not have to be the primary key field. I dont understand why you wish to sort the rows. in the above question you merely asked how to retrieve records  from the 100th record to the 300th record as they are stored in the table presently.

i was asking how to sort the table because that might give me an answer to my question, but the question remains the same:how to select from row 100 to row 300 without using order by.

But by definition the table is either clustered or a heap right?

If clustered use the existing cluster index for your sort (as this is how the records are stored)

If a heap then the top 100 is not guaranteed to be the same 100 records every time without a sort, in which case you may as well just get the top 200 as it has just as much meaning....

What are you trying to do? You keep saying the suggested answers take too much time. (Though several anwers are perfectly good in many situations)

It almost never makes sense to select rows 101 - 300 of a table without some kind of order (such as a primary key on ID) There is obviously some reason you don want rows 1-100

and if that reason is identifiable use a where clause

select top 200 *
from table
Where not 'identifiable characteristic of fist 100 rows'

Trust me when the dust has settled you will find that currently (all this will change with SQL 20005) the only practical way is to use temporary tables or variables of type table.  Your miles may vary...

i don't what to sort the table. i whan't to be able to select from row 100 to row 300 no matter what data is on that table. i might have only text fields and i whant to be able to select from row 100 to row 300 without sorting! i was thinking if there is a top and a buttom command there might be and a select from row to row command.

>>(all this will change with SQL 20005) <<
And of course that should have been SQL Server 2005 (aka Yukon)

You have a unique incremental PK?

SELECT y.*
FROM dbo.yourtable y
WHERE (SELECT COUNT(*) FROM dbo.yourtable y2 WHERE y2.pk<=y.pk) BETWEEN 101 AND 300


-Paul.

in which case i again revert back to add a field with an identity seed (you dont have to make this extra field a primary key) and then select where this field is between 100 or 300.
or alternatively like acperkins said if you dont want to add any fields to your table, load it into a temp table where you have an identity seed field as well and then select from the temp table where the identity seed field is between 100 and 300

refer back to my statment about a heap or clustered.

there is no such thing as an order which the rows are stored in unless there is a clustered index. In which case use that, it will not be slow.

Out of interest how long does this take:

select top 300 * from table

and what does the execution plan use?

If it uses a clustered index then just use that for your sort. If it doesn't use an index you have a slightly incorrect understanding on how data is stored in MS SQL

or u could use simons first answer but without the order by

SimonLarsen,

You really need to step back and think about this question and get over clustered indexes ...  

Take the Orders table in the NorthWind database.  Supposing you want the 201 to 300 row based on any of the columns: OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry or any combination of those columns.

I suspect that is what the questioner is asking.  Please correct me if I am wrong.

well
Simon is right when he is mentioning clustered indexes. When you have a clustered index on a table, the data is physically stored in the order of the clustered index.  So technically Simon is right. When selecting the top 100 or top 200 from a table with a clustered index your result WILL be the top n as per the clustered index if you do not specify a different order by. However the author of the question has specified that he cant use an order by. Thus he will ONLY get the top n as they were entered if the table is a heap (ie non-clustered index).

>>Simon is right when he is mentioning clustered indexes. <<
Grief!  I give up.

You would have to use a sort (implicit via cluster or explicit via order by) in that case though wouldn't you?

Otherwise you are just getting random data and may as well take the first 100? Unless there is a defining characteristic of the first 100 in which case a where clause would do the job?

Maybe there is something I am missing wildly, wouldn't be the first time ;)

going by a comment by yaz00us (author of question) somewhere above, the rows arent sorted, thus implying he does not have a clustered index. In which case the records are physically stored in the order they were entered, in which case getting row 100 to row 300 or for that matter row n to row y can only be done by means of simons first answer or alternatively with the use of an identity seed (either on the table itself, or via a temp table)              

If you have an index (clus or not) on a column -- any column -- you can do this:


SELECT TOP 100 *
FROM yourTable
WHERE indexedColumn > @lastValueShown
ORDER BY indexedColumn


For the first execution, set @lastValueShown to the minimum possible value (such as '' or 0).  After displaying the first 100 results, set the @lastValueShown to the last value shown -- the value of indexedColumn in the 100th row, that is, the last one displayed on the screen.  Say, for example, that the first screen showed:

1
3
11
...
248

For the next execution, set @lastValueShown to 248.

Notice that there will very likely *not* be an actual sort with this query if there is an index on the column: SQL is "smart" (sophisticated) enough to use the index, which it "knows" is already sorted.

Notice, too, that you *don't* have to change the query with this method.

Notice, finally, that if need an index on the column unless you are willing to wait for a table scan and sort to complete.

>>If you have an index (clus or not) on a column -- any column<<
Thanks, Scott for the clarification.  I thought I was losing it (again!).

select top 300 *
into #temp
from [table]

delete a
select top 100 *
from #temp a

select * from #temp

I just saw this and had to chime in:

", the data is physically stored in the order of the clustered index.  So technically Simon is right"

Technically, right now, this is correct, but you should NEVER count on the cluster index to guarantee sort order.  It works this way now, but it may not always work that way, and you will NEVER be guaranteed order without an ORDER BY statement on your query...Just because you have a clustered index on the table doesn't mean SQL Server will use it to return the records....

And, while we're at it, remember that it is actually only conceptually in order.  The data is *not* necessarily stored *physically* in order, even within one data page, but thru the use of pointers data can be retrieved in clus key order.

>>I would appreciate any comments by the experts that would help me in making a recommendation.<<
PAQ without a refund.

I agree with anthony