Post reply

Shall i create temp tables in production

  • December 21, 2012 at 1:20 am

    #272339

    Hi ALL,

    Shall i create temp tables in production ?

    Is there any problem please clarity

    Thanks

  • December 21, 2012 at 1:24 am

    #1570770

    The use of temp tables is a common practice even in production. There are many uses for a temp table some of which being to help with coding or various processes.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 21, 2012 at 1:28 am

    #1570775

    RK2012 (12/21/2012)

    Shall i create temp tables in production ?

    What kind of ?

    physical user table which you are going to use to store data temporaraliy?

    or #tmp or ##tmp ?

    or table vairable ( which are again a kind od table structure storage ) ?

    The question is why you want that ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 21, 2012 at 1:36 am

    #1570783

    For database information for all databases

    create table #dbsize

  • December 21, 2012 at 5:22 am

    #1570858

    All users have permissions to create local temporary (i.e. temp) tables by default. There are no issues with creating a single new temp table in and of itself. However, populating that table with data means using space in tempdb. This too might not be a problem if it is a small amount of data or the server is not otherwise busy. Just be aware that anything you do on the server will use a resource therefore your process will compete with other processes needing resources. Just be careful. If you have doubts the best advice is to run your code in a test environment that is representative of your production environment so you can be sure what you are doing will not adversely affect production processes.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • December 21, 2012 at 7:37 am

    #1570911

    RK2012 (12/21/2012)

    Hi ALL,

    Shall i create temp tables in production ?

    Is there any problem please clarity

    Thanks

    Like anything else, the answer is "It Depends". This isn't a simple question. The use of Temp Tables to hold interim results on complicated queries can save the day. The use of huge Temp Tables (or Table Variables) on "C.R.U.D." on a busy system can bring a system to its knees by reducing Page Life Expectancy to near zero but not always. Like I said, "It Depends".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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