how to restrict number of rows returned in a query

  • 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

  • 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. Selburg
  • 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. Selburg
  • 🙁

    Rowcount is not recognised.

  • Set ROWCOUNT n

    select * from project_lkup

    Set ROWCOUNT 0

  • check SELECT TOP n .... too might be enaugh for what you need


    Kindest Regards,

    Vasc

  • 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.

  • 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. Selburg
  • 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

  • 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. Selburg
  • 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