Temp Tbl

  • sizal0234

    SSCrazy

    Points: 2058

    Hi,

    I am new to using temp table and aware that they are created in Tempdb. However let us say if I use 'USE DB Test' code will it create in the test DB or still in Tempdb? I also wonder how they are beneficial. Example, SQL Server still has to create temp table ...does it not take any resource? Now, multiple online articles suggest to use only when data is huge and not for small amount of data..., is that because it is not worth creating the temp table for small amount of data and then again dropping it. What will happen if too many Users/Developers are creating Temp tables at the same time...will the Temp DB or log files fill up soon?

    Thanks!

  • John Mitchell-245523

    SSC Guru

    Points: 148360

    Temp tables will be created in tempdb regardless of your database context.  Yes, of course creating them takes resource - disk space, processor, memory, IO.  Do you have a link for these articles that suggest using temp tables only for large data?  There are some myths about table variables being created in memory and temp tables on disk, so that may be what those recommendations stem from.  If there's a lot of temp tables being created at the same time, then of course there's a danger that tempdb will fill up, but that depends on the size of your tempdb and the nature of the temp tables.

    John

  • ScottPletcher

    SSC Guru

    Points: 98168

    Now, multiple online articles suggest to use only when data is huge and not for small amount of data

    To put it simply, that's just wrong.  If you need to create a temp table for a single row, do so.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply