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!
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
November 8, 2019 at 4:17 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 26, 2019 at 11:03 am
Hi,
Using temp tables is beneficial when used in stored procedures and they come handy when you have to store temporary table values for further computations.
Yes, they consume storage and memory resources. If there is going to be a lot of users creating the temp table, then as a best practice, you should be keeping tempDB in dedicated storage drives that has good IO and storage space. You might also need to restart your DB services on a frequent basis in order to free up tempDB free space.
-- JigarShah
November 26, 2019 at 3:53 pm
You might also need to restart your DB services on a frequent basis in order to free up tempDB free space.
-- JigarShah
No, you should (almost) never need to restart the instance just to clear tempdb. That should definitely not be a "frequent" thing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply