Post reply

Temp tables vs derived tables

  • August 17, 2007 at 2:01 pm

    #373286

    One of the optimization tips that I use is to do away with using temp tables and just use a derived table in the form

    select drv_tbl.key,

    drv_tbl.sum(val)

    from ( ) drv_tbl

    group by drv_tbl.key

    What happens if I prefix a # i.e. use #drv_tbl as my derived table name? Does this mean that all though I am using derived tables I am losing the performance gain ; since appending a # shall make it a temp table??

  • August 17, 2007 at 3:28 pm

    #727762

    I was surprised that i could use # to alias a derived table. Looking into the execution times there was no difference (in time or plan). Further testing showed the ability to alias a table #table.

    select * from myTable #mytable

    Ergo I am guessing that #tablename is treated no different then a normal alias.

    daryl

Viewing 2 posts - 1 through 1 (of 1 total)

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