Table variable is not automatically dropped in TempDB

  • hi,

    I have created procedure like .

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo]. Script Date: 04/28/2013 01:09:39 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER proc [dbo].

    as

    begin

    declare @i table (id int)

    select * From tempdb.sys.tables

    end

    and after i ran the executed my proce

    exec b

    The result is :

    name objectid

    #023D5A04 37575172 and so on...

    i closed the above connection and after i opened new connection and ran the query like

    select * from tempdb.sys.tables

    it is showing the below result and table exists in tempdb with the name:

    The result is :

    name objectid

    #023D5A04 37575172 and so on...

    Question is:

    Why did not it automatically dropped when i ran procedure and even i closed the connection please help on this..

    Thanks,

    Dastagiri

  • It is effectively dropped, you can't in any way access the table variable after it goes out of scope. As an optimisation, SQL caches the definitions for temp tables & table variables so there's less work involved in recreating them.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The object still exist in under tempdb

  • Yes, it's an optimisation, SQL caches the definition and a single data page to reduce the work required to recreate it. The table variable's effectively gone, you cannot access it in any way, there's just a shell cached for future use.

    Why is it a concern?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I made up a quick test to verify Gila's suggestion.

    Very interesting outcome.

    Whoever is curios try to run this:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create proc [dbo].

    as

    begin

    declare @i table (id int, NAME nvarchar(150))

    select 'during exec', * From tempdb.sys.tables ORDER BY create_date DESC, name

    end

    GO

    select 'after create', * From tempdb.sys.tables ORDER BY create_date DESC, name

    GO

    exec b

    GO

    select 'after exec', * From tempdb.sys.tables ORDER BY create_date DESC, name

    go

    exec b

    GO

    select 'after exec again', * From tempdb.sys.tables ORDER BY create_date DESC, name

    go

    DROP proc [dbo].

    GO

    select 'after drop', * From tempdb.sys.tables ORDER BY create_date DESC, name

    GO

    WAITFOR DELAY '00:01:00'

    select 'after 1 minute wait', * From tempdb.sys.tables ORDER BY create_date DESC, name

    GO

    On my desktop (being effectively idle at the time) the table variable disappeared only in the "after 1 minute" resultset.

    Very different from the behaviour on SQL2000: object is gone immediately after SP is completed.

    _____________
    Code for TallyGenerator

  • Temp table caching was added in SQL 2005 to alleviate the contention on the TempDB allocation pages caused by frequent creation of temp tables. You can probably find a few articles on it, not something that's been widely written about though.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/30/2013)


    Temp table caching was added in SQL 2005 to alleviate the contention on the TempDB allocation pages caused by frequent creation of temp tables.

    I wish that would be a bottleneck in our systems...

    :hehe:

    _____________
    Code for TallyGenerator

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

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