We need to manipulate a set of records returned from a select query. This can be done in :

  1. Either by saving the whole record to a table variable and then do the required process.
  2. We can do like this

    Select * from
       (select * from table where conditions) AS results
       where results.RowNum between ....

    So what is the best method in this case.. using a table variable or the 2nd method. Is there any performance issues in using the second method compared to first?

  • What do you mean by manipulate? What are you doing after the initial select? – DForck42 Apr 12 '12 at 15:57
  • @DForck42 : to select a specified number of records according to page number to show in the front end.. – Mahesh KP Apr 13 '12 at 3:03

"it depends"

Using a table variable or temp table with requires overhead of creating populating this object

However, if the you require multiple processing steps then this is small compared to querying the same data over and over, especially as the query gets more complex.

Also, for multiple steps, using a table variable or temp table means working on the same set of data for all steps. Typically the underlying data usually changes as a result of other processes.

Finally, have you tried both techniques and benchmarked for your real situation? I use both approaches depending on all of the above factors. Sometimes it doesn't matter of course...

| improve this answer | |
  • Thank you.. Actually we need to get the result set according to the page number and me used the second approach. But all the procedures written previously uses the first approach and i don't know why they go for the first method. Thats why i posted it, to know whether the first got any performance advantage over second. – Mahesh KP Apr 16 '12 at 3:12

I know it's been posted on Stack Overflow before, but I can't find it. However, there is a pretty common method for paging data. It looks something like this:

declare @pagesize int/*number of records to show on each page*/,
    @pagenumber int /*which page you want to grab records from*/,
    @startrow int/*calculated to determine which row to start with*/,
    @endrow int /*calculated to determine which row to end with*/

set @pagesize=10
set @pagenumber=1
set @startrow=(@pagesize*(@pagenumber-1))+1
set @endrow=(@pagesize*(@pagenumber))
select @startrow, @endrow

select *
    select * ,
        row_number() over (order by PK_clID) as RowNumber
    from cdb.Clients
) c
where RowNumber between @startrow and @endrow
| improve this answer | |
  • Thank you.. Actually how we can implement paging of selected results is not my question. – Mahesh KP Apr 16 '12 at 3:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.