Temp table cache 4

Adding index vs DDL statement with inline index

 

 

use WideWorldImporters
Go

CREATE OR ALTER PROCEDURE dbo.temp_table_cache_test_inlineIndex
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp_inlineIndex
(InvoiceID int not null
,InvoiceMsg varchar(100) not null
,INDEX idx_nc_temp_InvoiceMsg_inline NONCLUSTERED(InvoiceMsg));

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

DROP TABLE #temp_inlineIndex;
END
GO
CREATE OR ALTER PROCEDURE dbo.temp_table_cache_test_laterIndex
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp_laterIndex
(InvoiceID int not null
,InvoiceMsg varchar(100) not null);

CREATE NONCLUSTERED INDEX idx_nc_temp_InvoiceMsg_after ON #temp_laterIndex
(InvoiceMsg)

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

DROP TABLE #temp_laterIndex;
END
GO

 

 

Test part :

 

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_inlineIndex;
GO 10

select entries_count
from sys.dm_os_memory_cache_counters
where name = N’Temporary Tables & Table Variables’;
GO
/* 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_laterIndex;
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, for inline index, the temp table is cached

 

entries_count
0

entries_count
0

 

No caching for CREATE InDEX, ALTER TABLE ..

 

S