July 26, 2017 at 5:59 am
Is there any advantage to test for temp db's in a stored procedure and then drop them as in this code? SQL Server drops the temp tables after it executes? Correct?
DECLARE
@DropTempDB NVARCHAR(MAX)
SET @DropTempDB =
''SELECT @DropTempDB = @DropTempDB + 'DROP TABLE ' + QUOTENAME( name ) + ';'
FROM tempdb..sysobjects
WHERE name LIKE '#[^#]%' AND OBJECT_ID( 'tempdb..' + QUOTENAME( name ) ) IS NOT NULL
IF @DropTempDB <> '' EXEC( @DropTempDB )
July 26, 2017 at 6:07 am
No, the table is dropped when the connection that created it finishes. So if the code in question will be executed multiple times from the same connection then yes, you do need to test for existence.
John
July 26, 2017 at 6:12 am
that script will drop all temp tables your session actually created;but not global temp tables that are in scope.
it would be handy for testing the guts of a stored procedure, where the procedure is bubbling up lots of temp tables.
instead of testing and dropping each table individually, you drop everything in scope
if it is still inside the procedure, if the procedure was nested inside other calls, it could potentially drop temp tables in an outer scope inadvertantly, but i would hazard a guess that it is a legacy of development, and not really needed.
so it avoids having to test each individual temp table
IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL
DROP TABLE [dbo].[#Results]
GO
CREATE TABLE [dbo].[#Results] (
[rid] INT NULL,
[rtext] VARCHAR(30) NULL)
Lowell
July 26, 2017 at 6:43 am
I should have mentioned this but, this code begins the procedure. So, there are no temp tables.
July 26, 2017 at 6:51 am
NineIron - Wednesday, July 26, 2017 6:43 AMI should have mentioned this but, this code begins the procedure. So, there are no temp tables.
yes I'd expect it to be right at the beginning of the code inside the proc. It make sme more and more sure it is a legacy of test development.
the procedure probably creates a bunch of temp tables....so the developer, when testing, would highlight the internal commands inside the procedure, and execute them, and not just execute the proc itself...he wants to visually inspect stuff, going line by line, re-selecting from the internal temp tables to verify accuracy of data, etc.
that snippet just clears out the temp tables that are created in the later parts of the script.
Lowell
July 26, 2017 at 7:04 am
NineIron - Wednesday, July 26, 2017 6:43 AMI should have mentioned this but, this code begins the procedure. So, there are no temp tables.
Then this code is pointless
😎
July 26, 2017 at 7:17 am
Thanx. I thought so.
July 26, 2017 at 8:44 am
It actually serves a very important function... troubleshooting. It's useless inside a proc but if someone is trying to troubleshoot the code in the proc, then it's nice to have the drop to do reruns of the code within the proc. I leave it in as commented out code as a courtesy to the next poor slob that may have to test/troubleshoot the code within the proc. It's especially helpful if you have a shedload of Temp Tables in the proc being troubleshot.
Other than that, it's a waste of clock cycles for a proc in production.
There's one exception to that rule... and that's to free up memory resources early on in the proc. For that, though, it's better and faster to just truncate the Temp Table.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2017 at 9:19 am
Jeff Moden - Wednesday, July 26, 2017 8:44 AMIt actually serves a very important function... troubleshooting. It's useless inside a proc but if someone is trying to troubleshoot the code in the proc, then it's nice to have the drop to do reruns of the code within the proc. I leave it in as commented out code as a courtesy to the next poor slob that may have to test/troubleshoot the code within the proc. It's especially helpful if you have a shedload of Temp Tables in the proc being troubleshot.Other than that, it's a waste of clock cycles for a proc in production.
There's one exception to that rule... and that's to free up memory resources early on in the proc. For that, though, it's better and faster to just truncate the Temp Table.
My guess is this is some residual from dev/testing/debugging where temp tables were used, seen that before.
😎
It's not going to be the Globe on Atlas's shoulders, it's just pointless and wasted effort.
July 26, 2017 at 12:21 pm
As noted, the code should be retained for debugging uses, but it should be modified some.
1) Be sure to add this statement before it:
SET @DropTempDB = ''
Or all the code will be for naught, since the result will always be NULL.
2) Use the newer view sys.objects not the obsolete view sysobjects.
3) Add "WITH (NOLOCK)" after the table to prevent blocking the code. The "NOLOCK" police here may caterwaul against it, but it will save you a lot of time by avoiding blocking of the system tables by "SELECT ... INTO #" statements that are common.
Thus, in summary. something like this:
SET @DropTempDB = ''
SELECT @DropTempDB = @DropTempDB + 'DROP TABLE ' + QUOTENAME( name ) + ';'
FROM tempdb.sys.objects WITH (NOLOCK)
WHERE name LIKE '#[^#]%' AND OBJECT_ID( 'tempdb..' + QUOTENAME( name ) ) IS NOT NULL
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply