Next step, altering table vs creating DDL from start
use WideWorldImporters
Go
CREATE OR ALTER PROCEDURE dbo.temp_table_cache_test_cleanDDL
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp_cleanDDL
(InvoiceID int not null
,InvoiceMsg varchar(100) not null
,someAdditionalField int null);
INSERT INTO #temp_cleanDDL(InvoiceID,InvoiceMsg,someAdditionalField)
SELECT TOP(10000) InvoiceID
, CAST(InvoiceID as VARCHAR(30))
+ ‘-‘ + CAST(NEWID()as VARCHAR(50))
,InvoiceID + 10
FROM Sales.Invoices;
DROP TABLE #temp_cleanDDL;
END
GO
CREATE OR ALTER PROCEDURE dbo.temp_table_cache_test_alterTable
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp_alterTable
(InvoiceID int not null
,InvoiceMsg varchar(100) not null);
ALTER TABLE #temp_alterTable
ADD someAdditionalField INT Null
INSERT INTO #temp_alterTable(InvoiceID,InvoiceMsg,someAdditionalField)
SELECT TOP(10000) InvoiceID
, CAST(InvoiceID as VARCHAR(30))
+ ‘-‘ + CAST(NEWID()as VARCHAR(50))
,InvoiceID + 10
FROM Sales.Invoices;
DROP TABLE #temp_alterTable;
END
GO
Above, it’s the creation part.
Next, will be the test part:
select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO
Exec dbo.temp_table_cache_test_cleanDDL;
GO 10
select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO
output:
entries_count
0
entries_count
1
So, the clean DDL will be cached.
/* now with out DROP */
DBCC FREEPROCCACHE;
GO
select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO
Exec dbo.temp_table_cache_test_alterTable;
GO 10
select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO
output:
entries_count
0
entries_count
0
The alter part, will NOT be caching.
S
Sql Server 