Caching جداول Temp در SQL Server 2014 بهینه شده است!

DECLARE @table_counter_before_test BIGINT;
SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
DECLARE @i INT = 0
WHILE (@i < 1000)
BEGIN
EXEC PopulateTempTable
SET @i += 1
END
DECLARE @table_counter_after_test BIGINT;
SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100), @table_counter_after_test - @table_counter_before_test)
GO

ALTER PROCEDURE PopulateTempTable
AS
BEGIN
-- Create a new temp table
CREATE TABLE #TempTable
(
Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
Col2 CHAR(4000),
Col3 CHAR(4000)
)
-- Insert 10 dummy records
DECLARE @i INT = 0
WHILE (@i < 10)
BEGIN
INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
SET @i += 1
END
END
GO

ALTER PROCEDURE PopulateTempTable
AS
BEGIN
-- Create a new temp table
CREATE TABLE #TempTable
(
Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
Col2 CHAR(100) INDEX idx_Col2,
Col3 CHAR(100) INDEX idx_Col3
)
-- Insert 10 dummy records
DECLARE @i INT = 0
WHILE (@i < 10)
BEGIN
INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
SET @i += 1
END
END
GO