December 13, 2007 at 11:07 am
Hi,
I have a query that returns lot of records. Is there any means by which i can restrict the number of rows returned?
I know in Oracle we can use rownum. Is there anything equivalent in Sql Server?
Thanks,
Rama
December 13, 2007 at 11:09 am
SET ROWCOUNT n
Where n is the number of rows.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 13, 2007 at 11:09 am
Oh, and to turn it off ...
SET ROWCOUNT 0
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 13, 2007 at 11:13 am
🙁
Rowcount is not recognised.
December 13, 2007 at 11:14 am
Set ROWCOUNT n
select * from project_lkup
Set ROWCOUNT 0
December 13, 2007 at 11:14 am
check SELECT TOP n .... too might be enaugh for what you need
Vasc
December 13, 2007 at 11:22 am
Basically, this SP is used by front end VB. So if the user wants records 1-50 displayed in the page then i wud use 2 variables like iStartRowNum = 1 and iEndRowNum =50.
SO am kind of confused how to implement it.
December 13, 2007 at 11:29 am
VB or VB.Net ?
If .NET, look into paging. That's not one of my strong areas, but I think that's what you need. and I'm not sure if it exists in VB6 or not?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 13, 2007 at 11:37 am
Ok i will look about Paging.
But i felt that i will restrict the records in the DB level than doing it in the Front end
December 13, 2007 at 11:41 am
Well, you could leverage the ROW_NUMBER() OVER() command in your proc and only return the rows needed that way.
But for some reason, I think it's more efficient to return the entire results to the webserver and handle the paging there. but that's your call.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 13, 2007 at 12:32 pm
If you want the first X rows, use "select top X ..." in your select statement.
If you want to return a range of rows, you'll need to have input parameters that restrict the first row and last row, based on either the key for the select, or on a row_number() function in the select.
If what you're trying to do is just reduce the amount of network traffic, and the work for the front-end app (web server or thick client), then either solution will work. If you also want to reduce the work for the database server, and get faster results, you'll need to use a first record and last record parameter in your where clause of the query.
For example:
select a, b, c
from dbo.table1
where id between @first and @Last
With @first being an input parameter of the first row you want and @Last being the last row you want.
You could build a proc that would return the Yth X rows, with Y being the sequence and X being the quantity (1st 20, 2nd 20, etc.). It would need to figure out which rows to return first, then return them, which can be done pretty easily with a table variable or temp table that picks up the IDs, then use the range of those for the actual query. It does mean querying the table twice, but since one is just the primary key it should be pretty fast.
In a query that otherwise returns a huge number of records, splitting it that way might very well be faster for all machines involved than returning the whole recordset and passing that through a network and then paging it. Your mileage may vary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply