Post reply

declare table

  • May 2, 2007 at 2:07 pm

    #123683

    in a procedure:

    when should i use the declare table command

    and when should i create a temp table (slect * from tb into #tbl)?

    thanks

  • May 2, 2007 at 2:46 pm

    #703936

    Best practices dictates that all ddl should be grouped in one point (the earlier in the proc the better).

     

    Without seing the code and requirements, it's hard for me to say much more than this.

  • May 2, 2007 at 4:46 pm

    #703960

    I think what he may be asking is which how to know which method to use.  Is this correct? 

    If this is in a stored procedure, you always want to use the create table command over SELECT INTO #tempTable and as Remi stated, group all DDL commands as close to the beginning of the stored procedure as possible.  Interleaved DDL causes stored procedure recompilation.  You may also want to consider using table variables in place of temp tables in certain instances.  Search SSC for table variables and you'll find many, many threads on that topic.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • May 3, 2007 at 3:36 am

    #704023

    Hi,

    the main difference for me is that you can do a

    insert #temp exec xxx

    but not a

    insert @temp exec xxx

    regards

    karl

    Best regards
    karl

  • May 3, 2007 at 7:07 am

    #704051

    Hi Sam,

    I used to use table variables (Declare @table) instead of temp tables all the time, primarily because they required less typing.  That is, until all the time I saved typing was more than eclipsed by all the time I was waiting for my stored procedures to complete.  Unless you are confident that you will have very few rows in your table variable, I think you're much better off using temp tables.

    Mattie

  • May 3, 2007 at 6:57 pm

    #704260

    Sam,

    Recommend you read the following URL... especially Q3/A3 and Q4/A4...

    http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

    ... personnally, I only use a table variable if I can't use a temp table... that would be only in a UDF.  I may even forsake that if I can figure out a different way to do what I need to do.

    --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)
    Intro to Tally Tables and Functions

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

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