• Grant Fritchey (10/8/2008)


    Except for a primary key, table variables have not statistics where as temp tables do. This means that they are treated as one row tables for all execution plans, which can lead to some seriously bad performance.

    Even the PK of a table var doesn't keep stats. Run the below code with the exec plan on and look at the estimated and actual rows on the index seek. When run in master on my server, I get Estimated rows 1, actual rows 191

    DECLARE @test-2 TABLE (

    id INT IDENTITY PRIMARY KEY,

    name VARCHAR(50)

    )

    INSERT INTO @test-2 (name)

    SELECT name FROM sys.columns

    SELECT * from @test-2 WHERE id between 10 and 200

    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