Manage select results in sql

Question

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?

Answer 1

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

Answer 2

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