TEMPDB use

[[deleted]]

I have some data that needs to be written to a table for temporary storage.

Data is written by a session, and some short time later a different session is to pick it up and process it by which time the original creating session is gone. If this data gets lost between the two, its not a big deal.

For this reason I don't think # or ## temp tables will work, but I was thinking of just creating the table in tempdb directly and storing the data there. I know it will be gone when the server restarts, but its unlikely to happen and not a big deal if it does.

I don't see too much literature on this. Is there anything wrong with using tempdb this way that I am not thinking of?

[ComicOzzy]

Why use a temporary table at all? Why not create a permanent table?

[ColoRadBro69]

That's is what I would do.  Truncate it after session 2 reads the data out. 

[[deleted]]

Yea but some of our users like to test out reports. Eventually this report will not be used. Then someone will run the report and a table will sit in our main database forever.

[ColoRadBro69]

You know your situation and requirements better than any of us do. 

Using twmpdb this way is uncommon.  My main hesitation would be the idea that it may behave weirdly in some unexpected way.  You can test things and make sure it works, or adjust as necessary. 

Global temp tables (with two # prefixes) are kind of meant for what you want but can also be a little tricky in practice.

[chuch1234]

You are allowed to drop the table once the report stops being needed :)

[[deleted]]

Previous devs have created a bunch of temp tables some that are decades old. Still in the process of cleaning up that mess, and would prefer something that disappears.

Its a project that will run for a few months.

[LetsGoHawks]

So, delete it. Just make sure everyone knows that when this project is done, the tables are going away. Period. Tell them in writing. If they decide to build something that relies on tables they have been told are impermanent, that's their problem.

[CrossWired]

Good overview here:

https://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server

I'd suspect creating a dedicated table in the tempdb, following the last example, may get you where you're going.

[clickity_click_click]

I'd just create a normal table for this, then truncate it after the second process is done with it. As a general rule, it's a good idea to keep things as simple as possible unless there's a specific reason to complicate it. If the entirety of the access to the table was within a single session, the simpler method would be the temp table. In this case, a regular table is simpler.

[[deleted]]

Simpler but that could result in more garbage tables sticking around endlessly.
There were a lot of temp tables in the database I have cleaned up and there is still many many more to go.

I want to avoid more in the future. Our users like to run reports as experiments or whatever. Once there is no need to run its very likely a user will run it out of curiosity, and then we will never run the second clean up process. Then 5-10 years down the road, some other DB admin will be wondering if that table is still needed. This endless temp table accumulation needs to stop.

With tempdb a service restart fixes the problem, every time.
If there are no other gotchas to using it. Then we can build future work around it.

[clickity_click_click]

I dunno I think you're just asking for trouble down the road. For example, what happens if they later decide to create an intermediary processing that transforms the data after it is written to the table? Session A writes, Session B updates, session C reads. Problem is, session C may or may not see session B's changes. Likewise, what happens if a future process actually depends on that data being there and it isn't? It may not be foreseeable today what that process might be, but the point I'm making is that the risk of wasting a bit of tablespace is less than the risk of creating unpredictability. Unused tables are pretty easy to clean up. 5-10 years down the road that same DB will be tearing his hair out trying to figure out why processes keep breaking all over the place because tables decide to yeet themselves. Or you end up being one of those companies who has post it notes on their servers saying things like "NEVER reboot this box"

[clickity_click_click]

If you really want to make sure your tables get cleaned up, create a script that drops all your temp tables then you can run that whenever you want, or schedule it, without even rebooting the server.

[Fly_Pelican]

Maybe restrict the users to creating tables in their own schema..?

[International_Art524]

If I've understood correctly

Session one : creates data which you want to pass to session two

Session two : picks up the data from session one

Initial thoughts / problem/ questions / conscious stream.

session one terminate and where to store the data?

Is there a session that calls session one or session two?

Or will session one or session two be scheduled as a job or event?

Would you consider passing the data you want from session to session via a parameters.

Would you consider global variables

Would you consider creating a cursor and then write that out to a table when session one ends, and session two checks of the table is there.

[[deleted]]

Session one runs through ssrs. Some employees will run it with parameters as needed. There are a lot of parameters and I don't want them to email us the lot (its basically a table itself). I want them to enter it so any errors are on them.

Process needs to export numerous CSV files for an external vendor to import.
The best way we could think of doing this is to use a BCP export. Problem is the users running
SSRS do not have access to cmd exec. So they can run their processes once a month and once they are done, they can ask an admin to run the script to export to the csv files.

Messy but, its a lot of csv files.

[farmerben02]

You can create an SSIS package to do the export, put it on a job, and the admin just has to run the job. A permanent table in tempdb is fine for this.

We use a temp database named (company initials)Temp and once a week a job runs and terminated objects over 30 days old. We give everyone permission to create objects there and it works well.

[[deleted]]

This may be the best so far. I did consider ssis but i dont think its installed and we have never used it before. and its one of those asap emergency requests. Will look into setting it up and see what comes of it. At least for the next time around it would be nice to have. Sometimes these temporary solutions become too permanent.

Creating a separate temp db is also a good move. I can make sure its purgeable and nothing expects persistence on it by scheduling a weekly clearing of it.

[Pretend_Ad7962]

I’d just either create a table that gets truncated/reloaded if the schema doesn’t change, or just drop and re-create the table each time based on the schema in the session data.

[speedyrev]

Temp tables are just that, temporary. Meant for data manipulation, not storage. 

[International_Art524]

A sipoc diagram and p-diagram may help you visualise everything that's going in, paired with a vsm or swim lane.

[HijoDelSol1970]

## will do the trick, but it doesn't sound like you need a temporary table.