GLOBAL TEMP TABLE OPTION ?

Question

GLOBAL TEMP TABLE OPTION ?

mar.ko

Default port

Points: 1451
More actions
November 9, 2015 at 1:22 pm

#322141

I need an option in CREATE TABLE that will allow me to create a database-specific global temp table
In other words, I need the temp table to persist along multiple stored procedures, yet not get clobbered by a proc executing in another database on the same SQL Server instance.
Does this exist ?
I guess one way around this is to:
1) check to see if the global temp table exists
2) if not, create one
3) if already created, only delete the records associated with that database name
This would require an attribute in the table that is set to DB_Name().
In that manner, the global temp table could be "shared" across databases.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I saw your other thread where your global temp table was intermittently getting dropped....

i would suggest simply creating a real, permanent table. maybe in your production db to avoid permissions issues, maybe in a [DBA_Utilities] database or something, and have your process use that.

you can create a table in tempDB without a # in it, which will stay until the server is restarted(when temp db gets rebuilt), but if it's semi permanent, just make it REALLY permanent instead.

CREATE TABLE tempdb.dbo.TableName(id int.....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mar.ko Default port Points: 1451 More actions · Answer 2

Thanks Lowell, and we are considering creating the cataloged tables.

The Tempdb option won't work since all of the databases would share the same table....

and that table would need to have the DB_Name() as an attribute to allow for sharing.

What I really wanted:

CREATE TABLE ##TEMPTARG

....

...

WITH

(DATABASE_SPECIFIC ON

)

In this manner, global temp tables would be shared only within the active database, not the entire instance !!

Our current workaround believe it or not is to stagger our job schedules between QA and PROD so they don't conflict.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok i see what you are talking about now;Test/QA/Prod are all on the same box, it looks like.

let me throw some ideas;

can you modify the temp table to have a new column called [DatabaseName]?

then each process can also do work in the global temp table WHERE [DatabaseName] = dbname()

any inserts would need to insert the new value as well.

if that doesn't feel right, how about three global tables.?

can you make the process sue a temp table named after the current database? ##TEMPTARGQA/ ##TEMPTARGtempProd/ ##TEMPTARGtempTest?

another possbility is to have something mailtain the glabal temps on a per db basis via synonyms.

you might even create a synonym for each database that is pointing to an alias, which changes under the covers for the global temp table.

CREATE TABLE ##TEMPTARGQA

CREATE SYNONYM [GlobalTemp] FOR tempdb.dbo.##TEMPTARGQA

then your process uses [GlobalTemp] instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mar.ko Default port Points: 1451 More actions · Answer 4

CREATE TABLE ##TEMPTARGQA
CREATE SYNONYM [GlobalTemp] FOR tempdb.dbo.##TEMPTARGQA
then your process uses [GlobalTemp] instead.

Brilliant, brilliant, brilliant.

In that way, I only have a small amount of dynamic SQL to build and execute.

Thanks Much Lowell !!!

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

mar.ko (11/9/2015)
I need an option in CREATE TABLE that will allow me to create a database-specific global temp table
In other words, I need the temp table to persist along multiple stored procedures, yet not get clobbered by a proc executing in another database on the same SQL Server instance.
Does this exist ?
I guess one way around this is to:
1) check to see if the global temp table exists
2) if not, create one
3) if already created, only delete the records associated with that database name
This would require an attribute in the table that is set to DB_Name().
In that manner, the global temp table could be "shared" across databases.

Quick question, why does it have to be a temp table? Tempdb is somewhat a limited resource and shared between all databases on the server, any reason why this cannot be a normal table in each database?

😎

mar.ko Default port Points: 1451 More actions · Answer 6

Quick answer:

Desperately trying to avoid "catalog clutter" with stupid tables that show no real business purpose.

In this case, these are tiny tables that have meaning only during processing.

More importantly, why isn't MSFT doing something to improve TempDB and temp tables ?

I was reading about the 2016 feature of "Temporal Tables" and wasn't sure they would even be useful for solving my problem.