Help obtain a window of rows from a table

Hi,
I have a client program in vb.net that access a SQL server database. Each
time the client program need some data it retrieve the whole table, so it is
pretty slow.
I wonder if it is possible, for the client, to retrieve only a window of
rows around the actual value he is using. That is, if he is actually in row
4000 he will retrieve from row 3000 to 5000 but not the complete table. If
this is possible what I need is something like:
1) The client send SQL-Server a string with the actual ordering and the ID
of the actual row.
2) SQL-Server order the table following the order specified in the string
send by the client.
3) Using the ordered table SQL-Server "find" the ID of the actual row.
4) SQL-Server return a number of rows before and after the Id of the actual
row (no idea how to do this).
Any help.
Thanks,
JamesHi James,
Yes - its basically paging.
The basics are this...
declare @.results table (
idrow int not null identity,
yourresultcol1...
yourresultcol2...
)
insert @.results ( yourresultscol1, yourresultscol2 )
select yourresultscol1, yourresultscol2
from table...
where ...
order by ...
select *
from @.results
where idrow between @.start and @.finish
I know its not a complete working example but does that give you enough
idea?
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||Sorry, i forgot to mention, in SQL 2005 its a whole lot easier.
We have the rownumber() function and cte that does it all for us - there are
some really useful examples in bol.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uhnuUbNHGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi James,
> Yes - its basically paging.
> The basics are this...
> declare @.results table (
> idrow int not null identity,
> yourresultcol1...
> yourresultcol2...
> )
> insert @.results ( yourresultscol1, yourresultscol2 )
> select yourresultscol1, yourresultscol2
> from table...
> where ...
> order by ...
> select *
> from @.results
> where idrow between @.start and @.finish
> I know its not a complete working example but does that give you enough
> idea?
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "James" <info@.pricetech.es> wrote in message
> news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>|||James
I think Tom Moreau had already answered the same or almost the same question
a few days ago. Pls search on internet
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||James wrote:
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
is
> pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in ro
w
> 4000 he will retrieve from row 3000 to 5000 but not the complete table. If
> this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the actua
l
> row (no idea how to do this).
> Any help.
> Thanks,
> James
Take a look at:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--