First, consider the possibility to get all rows to the client and do
the paging there. This has the distinct advantage of saving turnaround
time each time you need more data. Obviously, this method takes its
toll in terms of memory, and if the complete result set can be several
thousands of rows, you might still have to do paging. Or just cut when
you get more than, say, 2000 thousand rows. To this end, SQL Server
offers the SET ROWCOUNT command which takes either a constant or a
variable as parameter. When considering this, not only the number of
rows has significance, but also the size of them.
If your data has a single identifying primary key, you can use this for
the first SELECT:
SELECT TOP 100 col1, col2, .... FROM tbl ORDER BY keycol
Then for the next batches you use:
SELECT TOP 100 col1, col2, .... FROM tbl
ORDER BY keycol
clause that I use here, is another way to delimit the size of the
result set. In difference to SET ROWCOUNT, TOP can only take a constant
If your data has a multi-column key, the above method can still be
used, but the WHERE clause becomes messier the more and more key
columns you have.
Another possibility is to use a temp table:
CREATE TABLE #tmp (rowno int IDENTITY(1, 1) NOT NULL,
-- other cols)
SELECT col1, col2 FROM tbl ORDER BY <your criteria>
ORDER BY rowno
A variation of this theme is to create a permanent table, which you
drop when the paging is no longer in use. The advantage here are
1) By running the basic SELECT once, you gain performance for next
2) If new data is inserted while the user is paging, this method will
not miss rows or include a row twice, because what was row 99 in the
sort order has become row 101.
Finally, if you are using ADO, I believe there are paging functions
within ADO. Never used them myself, though.
I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )
Two comments:Quote:> All the above solutions can be used. One other solution is -CURSORS
> Although cursors should be used as last alternative.
> Using cursors also it is possible to fetch range of records.
1) Cursors will be MUCH slower than a set solution.
2) As Joe Celko and possibly others would point out, if you're trying to get
rows 100-201 or something like that, question what you are doing. In theory
a SQL table is an unordered set of records. Key word being unordered.
Imposing an "unnatural" order shows a "non-set" type thinking.
Quote:> All the best
> Kamal Arora
If I have a table called MyTable with two columns
defined as CHAR(1). Let's say I have the following
data in MyTable:
How would I write a SELECT that will return the
data in the following format:
Any help I can get is greatly appreciated.