asked
What is real time application of global temporary tables? Any example please.
Today, I was doing testing on nested procedures to check scope of temporary(global/local) tables from outer sproc to inner sproc. However, I am used to local temporary table for holding temporary data for processing inside any batch or sproc. But, I have never used global temp tables in real time working. Can any one explain me any situation when I can only rely on global temp table as option?
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
One scenario where I've used global temporary tables a lot, is when using Database Mail and `sp_send_dbmail`. In a stored procedure I execute a complex query that determines some data to send in an email - say for example an alert - if there is no data I don't send the email. On calling `sp_send_dbmail`, I don't want to execute the same complex, expensive query, I want to access that simpler, smaller result set I've just generated, but I can't use temporary tables to 'pass' the data over as the query specified to `sp_send_dbmail` is executed in another connection. Global temporary tables work perfectly here. From BOL: >[ **@query** = ] 'query' Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
You just demonstrated one such use. So the global temp table is defined with two hashes as ##MyTemp table and it lives in the Tempdb until you delete it explicitly or the database restarts. Other common usage patterns are commonly found in ETL e.g. when there is a need to share state or data across two connections. For example you want to load a file to a table and perform some data corrections and then from that table push the result to the ultimate target. Oftentimes the temp tables should be well indexed after the load or at least have a clustered index. PS: Also remember about temporary table variables that are even more feature rich.
Slight correction, global temp tables live until the last session that references it disconnects, then it gets cleaned up like a regular temporary table. So, if it is needed, you have to make sure that anything that references it has the ability recreate it, just in case it was dropped.
Thanks Kev , Grant It cleared my doubts
16 People are following this question.
