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


Either by saving the whole record to a table variable and then do the required process.
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?
"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...