Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Table Variables and performance RE: Table Variables and performance

  • October 10, 2008 at 2:46 am

    #883311

    Michael Earl (10/8/2008)

    While it is running, look for tables in tempdb:

    SELECT * FROM TempDB..Sysobjects WHERE Type = 'u'

    Temp tables and table variables are added to the TempDB system tables when they're created, not when they spill to disk.

    Easy to demonstrate (run on a server without other usage, so other people's temp tables don't mess up the test)

    DECLARE @BatchStart DATETIME

    SET @BatchStart = GETDATE()

    DECLARE @test1 TABLE (id int)

    CREATE TABLE #Test2 (id int)

    SELECT * FROM TempDB.sys.tables where create_date >= @BatchStart

    DROP TABLE #Test2

    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