Temp table cache 2

First test  DROP TABLE statement vs no DROP TABLE statement.

 

I have two stored procedures :

use WideWorldImporters
Go

CREATE OR ALTER PROCEDURE dbo.temp_table_cache_test_WithDROP
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp_WithDrop
(InvoiceID int not null
,InvoiceMsg varchar(100) not null);

INSERT INTO #temp_WithDrop(InvoiceID,InvoiceMsg)
SELECT TOP(10000) InvoiceID
, CAST(InvoiceID as VARCHAR(30))
+ ‘-‘ + CAST(NEWID()as VARCHAR(50))
FROM Sales.Invoices;

/* express drop table statement*/
DROP TABLE #temp_WithDrop;
END
GO
CREATE OR ALTER PROCEDURE dbo.temp_table_cache_test_noDROP
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp_noDrop
(InvoiceID int not null
,InvoiceMsg varchar(100) not null);

INSERT INTO #temp_noDrop(InvoiceID,InvoiceMsg)
SELECT TOP(10000) InvoiceID
, CAST(InvoiceID as VARCHAR(30))
+ ‘-‘ + CAST(NEWID()as VARCHAR(50))
FROM Sales.Invoices;

–DROP TABLE #temp_noDrop;
END
GO

 

Now, let’s call them and see , with the help of a dmv sys.dm_os_memory_cache_counters

select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;

 

 

DBCC FREEPROCCACHE;
GO

select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;

Exec dbo.temp_table_cache_test_WithDROP;

select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO

 

output of it :

entries_count
0

entries_count
1

 
/* now with out DROP */
DBCC FREEPROCCACHE;
GO

select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;

Exec dbo.temp_table_cache_test_noDROP;

select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO

 

the same output:

entries_count
0

entries_count
1

 

So, DROP TABLE or not , will not prevent caching.

 

 

S