Does high vol. of #tmp tbls(hundred over) effect CPU Memory

Yes,
but once execution is done then it will free the memory resources and you dont need to drop the table.

yes

Please refer
http://www.sql-server-performance.com/2012/sql-server-t-sql-tuning-temp-table-union/

hope this will help you.

Shehzad12y ago

Hi Abrar / Jignesh,

Thanks for your reply...But my concern is CPU Memory utilization....

As per Articles i have read about table Variable it is partially created on Memory ' Is my understanding correct?

Regards

Shehzad

Jignesh Trivedi12y ago

hi,

yes agree with Khan Abrar Ahmed.
but it automatically dropped by SQL server once it finish execution scope. We can also an DROP a temp table explicitly (after finish task simply drop it).

Instead of Temp table you can use temp variable or CTE.
hope this will help you.

Khan Abrar Ahmed12y ago

Yes, instead of creating the temp table create table variable if you are not using the table in cross script.