Post reply

Temp Table Strategy

  • January 12, 2021 at 4:09 pm

    #3831510

    1. Use a SQL start up proc to create the table.  You could consider a regular table name in tempdb to make sure the table doesn't get dropped even if no connection is currently using/referencing it.
    2. I thought all users had permission to create tables in tempdb.  But maybe not.  Or maybe it's just global temps they can't create?  At any rate, I allow users to freely use tempdb (if they abuse it they may be restricted).  You can add the desired permissions to tempdb for specific users in the SQL start up proc as well.

    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".

  • January 12, 2021 at 4:49 pm

    #3831524

    Is there a reason for you thinking that tempdb is the best place for your new table? Why not put it into an existing User database instead? Or create a new Reporting (or whatever) database and use that. It then becomes part of your usual development/source control process and you do not have any problem with recreating the table after a restart.

  • January 12, 2021 at 5:17 pm

    #3831562

    Are you using SSRS for the reports - or something else?  If SSRS - then you have the option of using shared datasets and can control when and how often the shared dataset is updated/refreshed.

    If not - and you are going the route of 'permanent' tables in tempdb, wouldn't it be cleaner to just build permanent tables and cleanup processes in a user database?  Since you have to have cleanup processes in this approach then it shouldn't matter whether you are using a permanent table in tempdb or a permanent table in a user database (I would think).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 4 (of 4 total)

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