Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Apr 13 '12 at 3:03

2 Answers 2

up vote 8 down vote accepted

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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