Post reply

difference between # and ## temp tables in sql server

  • July 30, 2012 at 8:56 am

    #392852

    hello

    please tell me difference between # and ## temp tables in sql server

    as per my knowledge # temp table is only for that session, when we closed that spid then its no longer exist and ## temp tables is exist for different session means for different SPID.

    but i have problem i create ## temp tables before 2 days and when i come back to work, i saw that ## temp tables does not exist.

    so please help me to understand properly.

    Thanks

  • July 30, 2012 at 9:01 am

    #1519209

    ## temp tables only continue to exist if another session is using them, otherwise they get dropped too.

  • July 30, 2012 at 9:02 am

    #1519212

    #temp tables are available ONLY to the session that created it and are dropped when the session is closed. ##temp tables (global) are available to ALL sessions, but are still dropped when the session that created it is closed and all other references to them are closed.

    From BOL:

    Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

    Jared
    CE - Microsoft

  • July 30, 2012 at 9:05 am

    #1519215

    Thanks to

    laurie-789651 and SQLKnowItAll

    Appreciate

  • May 6, 2014 at 10:00 am

    #1711156

    Difference between # and ## temp tables has been greatly explained on below link with examples.

    http://www.passionforsql.com/2014/04/difference-between-temp-single.html

  • May 6, 2014 at 10:01 am

    #1711158

    Difference between # and ## temp tables has been greatly explained on below link with examples.

    http://www.passionforsql.com/2014/04/difference-between-temp-single.html

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

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