SSRS – Using Tempdb Tables, Database Tables, or CTE for Reporting

ssrstempdb

I am about to start a project for SSRs report base on a USP. The procedure will have to use two cursors to compare orders v stock position (similar to a mrp run ): I am thinking of storing the data into a table that can be used In the ssrs report.

So the question is for a result set of no more than a few hundred lines what sort of table should I use. One in the production dB. A temp table in tempdb or can you use cte ?

The table will be dropped each run of the USP/ report

What comments are there

Thanks

Best Answer

If your data needs to be persistent over a failure & restart then a "real" table in your database would be best. You need a mechanism to isolate one user's execution of this from another's and to tidy up nicely.

If data is to be disposed of if the SP crashes and will be re-created on a restart then a table-valued variable would be adequate. These are not indexed, however. If the data will be re-read according to different predicates this may be a problem.

Temporary tables e.g. create table #abc... will keep one user's data isolated from another users'. The tables will be tidied away at the end of the SP. Indexes and statistics are held for #Tables so these perform better for larger row counts or multiple access paths.

Related Question