Is it really necessary to explicitly delete the temporary table defined in the stored procedure of the SQL Server database (drop table #tableName)?

The source of this article: http://www.cnblogs.com/wy123/p/6704619.html

Issue background

In a write SQL Server stored procedure, if a temporary table is defined in the stored procedure,
Some people are used to explicitly delete a temporary table defined in the process at the end of the stored Procedure (drop table #tName), some people do not have this habit,
For people who do not know the truth or who like to think about it, the temporary table defined in the stored procedure should not be actively deleted, why?
Or is it more canonical to delete a temporary table when the stored procedure is finished?
More than one person asked this question, to say the truth, I really do not know, just think, explicitly deleted or not delete the line, the temporary table after the current session disconnects will automatically release
So the temporary table defined in the stored procedure, after use, is deleted or not deleted? Is there any difference between explicit deletion and no deletion?
This article will be a superficial analysis of the problem, if there is no place, also hope to point out, thank you.

The table structure of the temporary table in the stored procedure is also slow and reused

So what's the difference between explicitly deleting an explicit deletion or not deleting it?
This involves a temporary table cache of knowledge points, first of all to see what is a temporary table cache.
The Cache staging table is a new feature since SQL SERVER 2005.
When creating a temporary table, you need to write data to the temporary library system tables (metadata, table structure information for the temporary table), which requires a certain amount of resource consumption, just like normal additions and deletions.
In cases where conditions are met (what are the conditions that need to be met),
The metadata for the temporary table is saved in the system table in the temporary library (tempdb) every time the user requests are completed (of course, the user requested SQL contains temporary tables)
Although the temporary table created by the current session is not visible to the user, it will not be accessible after the session is disconnected or the temporary table is deleted (drop).
But when the new session invocation also contains the code that creates the temporary table, SQL Server internally reuses the temporary table created at the time of the previous session without having to define the temporary table again.
This saves some of the resources that are consumed by the steps to create the table.

Above is the theory, below to do a small experiment to demonstrate the above theory, first of all the different sessions between the temporary table "reuse" phenomenon.
First, you need to use the system view Sys.dm_os_performance_counters to determine the number of times the temporary table was created, and the name of the counter in the system table is: Temp Tables Creation rate.

Create the following stored procedure, which defines a temporary table in the stored procedure.

Create procedureproc_testtemptable asbegin        Create Table#t20170413 (Col_1varchar( -), col_2varchar( -)    )    Insert  into#t20170413Values('AAA','BBB'); Select *  from#t20170413--SELECT * from tempdb.sys.tables where name ' #t20170413% 'End

After the stored procedure is created, the first time you execute, observe a phenomenon, as follows

It is clear that the temp Tables Creation rate counter in the Sys.dm_os_performance_counters system table has been added 1, which means that a temporary table creation action occurred during the execution of the stored procedure
Then continue executing the above code again

The same code, this time the temp Tables Creation rate counter in the Sys.dm_os_performance_counters system table does not add 1,
Why is it obvious that the temporary table is defined in the stored procedure, which executes once, Temp Tables Creation rate plus 1, and then executes again without adding 1?
This is the phenomenon of temporary table reuse (strictly speaking, the table structure of the temporary table or the table definition, but not the data).
The first temporary table is reused because the temporary table was created the first time the stored procedure was executed, and then the stored procedure is executed again.

How does that prove that the second time the stored procedure was executed, the temporary table created for the first time was reused?
Slightly modify the stored procedure, add a code to the stored procedure, query the temporary library in the temporary table information

Then execute two times the following code, the following is the result of the second execution (below will explain why the result of the second execution),
When the temporary table is reused, the information of the current temporary table is queried, and the number of temporary table creation is not increased, that is, the temporary table is reused.

Now that the temporary table is reused, the temporary table must exist in the system table of the temporary library, so how can we prove that the temporary table of this stored procedure is in the temporary library?
The ID of the temporary table shown above is-1297292959, and here is the table information for query id =-1297292959 in the temporary library, and it is found that there is a table.
This table information in the temporary library is not the same as the name and Modify_date, Modify_date is observed to be the time that the temporary table is reused, that is, the temporary table is reused once to modify the Modify_date
The other information is exactly the same, which means that after the first execution of the stored procedure, the temporary table that it created is cached (as for the name, which is explained later),
When you execute the stored procedure again, you can reuse the table structure of the temporary table that was created the first time the stored procedure was executed.

Is it useful to explicitly delete temporary tables in a stored procedure?

Make the following changes to the stored procedure above to explicitly delete the defined temporary table before the end of the stored procedure

Then execute the following test code again, note the result of the second execution (below will explain why the result of the second execution)

Then continue querying the system tables in the staging library for the above IDs, and find that the temporary tables still exist in the system tables, even if they are explicitly deleted in the Stored Procedure (drop table #t20170413)

This means that even if the temporary table is explicitly called in the stored procedure, the temporary table will still exist in the system table of the temporary library, that is, the temporary table will still be cached.
The temporary table objects are cached in the system tables of the staging library because they are not actually deleted because they are explicitly deleted in the stored procedure.
The name of the temporary table queried in the session differs from the name of the temporary table that is queried in the system table because the temporary table is created from the cache, and the internal process of renaming the temporary table on the current session is only occurring internally.

It turns out that:
For the temporary table of the stored procedure, it satisfies the cacheable premise (only the table structure, of course, does not include the data of the temporary table).
You delete, or do not delete, he will be cached in the temporary library, not because the explicit drop temporary table, temporary table will be really deleted, this is the SQL Server specifically for this optimization, you really do not need to delete temporary table and worry about or tangled
Back to the beginning of the question, there is no need to explicitly delete the temporary table in the stored procedure has the answer: the temporary table for the creation of the stored procedure, no need to delete, for the cache of temporary table objects, want to delete also deleted!

Temporary tables defined in the stored procedure are reused by the cache only if certain conditions are met

As stated above, the reuse of temporary tables is to meet certain conditions, the following conditions will cause the temporary table can not be reused

1, there is a naming constraint when creating a temporary table (this is a very fucked-up, not just a cache problem, once encountered a pit, a chance to demonstrate)
2, perform DDL operations after temporary table creation, such as creating indexes, but this DDL does not include drop temp table and truncate temp table
3. Temporary tables created in dynamic SQL mode
4, a temporary table created within a different scope should be a temporary table that the stored procedure calls another stored procedure and another stored procedure definition, which has not been specifically studied
5, stored procedures run with recompile recompilation

For example, in the above stored procedure, after the temporary table definition, create an index,
This will result in temporary tables that cannot be reused, in which case the staging table will not be cached (in the temporary library) after the session is disconnected, regardless of whether you delete or delete the stored procedure.
This does not demonstrate that you are interested in testing yourself

Explain another question:
Now that you think you can't delete a cached temporary table, under normal circumstances, what happens to the cached temporary table?
This is the second run, because after the stored procedure is rebuilt (create or alter), the temporary tables defined in the stored procedure are cleaned out
Only the stored procedure is rebuilt, and after the first execution, the cached temporary table is reused at the second execution time
Of course this is also easy to verify after caching the temp table and then alter the stored procedure,
Then, according to the ID of the cache temp table, the data of sys. Tables is queried in the temporary library, and the cached table is deleted after 1-2 seconds (personal test verified)
Alternatively, you can explicitly execute DBCC FREEPROCCACHE and delete the cached temporary table.
In fact, it is not difficult to understand that the cached object is bound to the execution plan cache, if the execution plan itself does not exist, then the cached temporary table object will be processed.

Can temporary tables be reused in the case of concurrent execution?

The same temporary table is certainly not reused between concurrent threads, and if not, SQL Server does not have to be mixed up, and each concurrent thread creates its own temporary table.
The following is a case of temporary tables generated by tempdb in the concurrency scenario, where each thread invokes a stored procedure to produce a temporary table suffix that is not the same.
When a stored procedure is called concurrently, each thread produces its own temporary table, and the reuse of the temporary table occurs after the execution of the current thread completes, and the cached temporary table is reused when the other session calls the stored procedure again.
Since this article does not specifically describe the temporary table, there is not much to say.

　　

Performance tests for explicitly deleting temporary tables or not

Since the above said, if the temporary table defined in the stored procedure satisfies the condition that the temporary table is cached, the temporary table will be cached in the same way if the temporary table is deleted in the stored procedure
So, is there any difference in performance if you really specify an explicit delete temporary table operation, with no explicit deletion of the temporary table?
Holding the attitude of the data to speak, respectively, in the stored procedure is not deleted and explicitly delete the temporary table, with Sqlquerystress did a series of performance tests
The results are as follows

Do not explicitly delete temporary tables	To explicitly delete a temporary table

The test results are as follows

　　

Part of the test process (do not waste the blog Park Image server resources, arbitrarily cut off two pieces)

　　

From the test results, there are some differences, but the difference is very small,
The first set of test results 5,000 calls yielded a 0.07-second gap
The second set of test results 20,000 calls generated a 0.35-second gap, the average to a gap in the subtle level, even if the explicit call to delete, the performance is a little impact, but this effect is harmless.
However, this internal original must be clear, there is no need to delete, as well as the reason, this is the principle of the problem!

As to the space occupied by temporary table data, it is not to say that explicit deletion is released, not deleted, not released, there should be a background process to do this work, personal advice not to worry about this problem.
When writing stored procedures, it is much better to write a little bit more SQL statements than to tangle with them.

A more verbose sentence:
Some people's ideas are ingrained, for those who are accustomed to deleting temporary tables feel the need to do so "normative", "professional", although he has no precise reason to say that explicit deletion of temporary tables is necessary.
But you have to tell him there is no need to delete the temporary table, it will provoke him, a lot of programmers are like this, you deny his deep-rooted view of the time, he was very annoyed.
Biologically speaking, this is a "printed accompanying for", perhaps the original teacher said, or the object of worship to do so, or listen to the tall man said that it is better, and then he has been doing so and firmly believe that there is no doubt.
Of course, including myself at some point, I have this kind of behavior, my mind is cured by some experience, and then I am bound by my cognition.
But for the harmless problem, he went with him, there is no need to persuade him, do not make him feel you amateur, hope that small partners to discern, good seems to digress ...

Summarize:　　

This article starts with the need for explicit deletion of temporary tables in stored procedures, briefly introduces the phenomena and prerequisites of temporary table reuse, and whether it is necessary to explicitly delete temporary tables.
At the same time, the performance problem of temporary table explicit deletion is tested in the case that the temporary table is satisfied with reuse, and it is not recommended to explicitly delete the temporary table defined in the stored procedure, regardless of whether or not it can be reused.

Reference Link: https://www.mssqltips.com/sqlservertip/4406/sql-server-temporary-table-caching/
Http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

Is it really necessary to explicitly delete the temporary table defined in the stored procedure of the SQL Server database (drop table #tableName)?