TexCopy Sql Server 2005

I use temp table on most of my stored procedures for storing the intermediate results, but there was one particular stored procedure some one else in my team created where he used a table variable; He was creating the table variable within a loop, populate the data and processed it, assuming that with each iteration, sql server is going to create another object, the query didn’t generate any error neither did it produce expected result. Since I cannot post the exact query, I created another procedure for this post

ALTER PROCEDURE dbo.testTableVariable 

@i int 

AS

BEGIN

    SET NOCOUNT ON

    WHILE @i > 0 

    BEGIN 

        DECLARE @tab TABLE ( i int ) 

        INSERT INTO @tab SELECT @i 

        SET @i-=1 

    END

    SELECT * FROM @Tab

END

GO

EXEC testTableVariable 2 

The output showed both the records, it was supposed to show just the second record ‘1’.


I wanted to test whether the same behaviour exist for temp table, as expected it throw the error , so I modify my query slightly

CREATE PROCEDURE dbo.testTemp1
AS
BEGIN
   SET nocount ON
   CREATE TABLE #temp ( i UNIQUEIDENTIFIER)
   INSERT INTO #temp SELECT NEWID()
   SELECT OBJECT_ID ('tempdb..#temp'),* FROM #temp
   DROP TABLE #temp
END
GO
EXEC dbo.testTemp1
EXEC dbo.testTemp1
EXEC dbo.testTemp1
EXEC dbo.testTemp1

The result showed me, even though SQL first deleted the temp table, for the subsequent create table statement, it created an object with the same ‘object id’, how come is this possible ? Seems to me like it was caching the temp table definition, that's why when it again execute the same ‘CREATE TABLE’ statement, gets the same object id; to ensure that this is applicable for the indexes and the stats on the temp table I modified my query slightly to see whether it stores those objects.

CREATE PROCEDURE dbo.testTemp11
AS
BEGIN
    SET nocount ON
    CREATE TABLE #temp ( i UNIQUEIDENTIFIER PRIMARY KEY )
    EXEC tempdb..sp_helpIndex '#temp'
    INSERT INTO #temp SELECT NEWID()
    SELECT OBJECT_ID ('tempdb..#temp'),* FROM #temp
    DROP TABLE #temp
END
GO
EXEC dbo.testTemp11
EXEC dbo.testTemp11
EXEC dbo.testTemp11
EXEC dbo.testTemp11

As you can see, the object ids and the names are the same, seems like these type of behaviour  can cause the even parameter sniffing.




The next step was to confirm these are on cache, I used the following statements to do this

SELECT    t.name,    t.[object_id],    t.type_desc,    t.create_date

FROM tempdb.sys.tables AS t

WHERE    t.name LIKE N'#________';

SELECT    pc.[object_name],     pc.counter_name,     pc.cntr_value

FROM sys.dm_os_performance_counters AS pc

WHERE    pc.[object_name] LIKE N'MSSQL%Plan Cache%'    

AND pc.instance_name = N'Temporary Tables & Table Variables'    

AND pc.counter_name IN (N'Cache Object Counts', N'Cache Objects in use');

And my further research revealed dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS.  None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).