zLc7TTf9

1--https://www.sqlshack.com/using-memory-optimized-tables-to-replace-sql-server-temp-tables-and-table-variables/
2DROP PROCEDURE IF EXISTS dbo.TempDB_TempTableTest;
3DROP PROCEDURE IF EXISTS dbo.MemoryOptTableTest;
4DROP PROCEDURE IF EXISTS dbo.PhysicalTableTest;
5DROP PROCEDURE IF EXISTS dbo.VariableTable;
6DROP PROCEDURE IF EXISTS dbo.MemoryOptVarTable;
7GO
8
9DROP TABLE IF EXISTS [MemoryOptTempTable];
10DROP TABLE IF EXISTS [PhysicalTempTable];
11DROP TYPE IF EXISTS MemoryOptTabVar;
12GO
13
14CREATE TABLE [MemoryOptTempTable]
15(
16 [ID] INT IDENTITY(1,1) NOT NULL ,
17 [First_Name] nvarchar(10) NULL,
18 [Last_Name] nvarchar(10) NULL,
19 
20 CONSTRAINT [PK_MemoryOptTempTable]  PRIMARY KEY NONCLUSTERED 
21(
22 ID ASC
23)
24)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
25GO
26
27CREATE TABLE [PhysicalTempTable]
28(
29 [ID] INT IDENTITY(1,1) NOT NULL ,
30 [First_Name] nvarchar(10) NULL,
31 [Last_Name] nvarchar(10) NULL,
32 
33 CONSTRAINT [PK_PhysicalTempTable]  PRIMARY KEY NONCLUSTERED 
34(
35 ID ASC
36)
37)
38GO
39
40CREATE TYPE MemoryOptTabVar as TABLE (
41   [ID] INT IDENTITY(1,1) NOT NULL ,
42   [First_Name] nvarchar(10) NULL,
43   [Last_Name] nvarchar(10) NULL,
44   INDEX IX_MemoryOptTabVar_ID NONCLUSTERED (ID)
45) WITH (MEMORY_OPTIMIZED = ON)
46GO
47
48CREATE PROCEDURE dbo.TempDB_TempTableTest --- Normal SQL temp table
49AS
50BEGIN
51SET NOCOUNT ON;
52 CREATE TABLE #NorTempTable (ID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, [First_Name] 
53nvarchar(10) NULL,   [Last_Name] nvarchar(10) NULL)
54
55 DECLARE @cnt INT = 0;
56 WHILE @cnt < 1000
57 BEGIN
58	INSERT INTO #NorTempTable (First_Name,Last_Name) VALUES ('Sanya','J')
59	SET @cnt = @cnt + 1;
60 END
61
62 DROP TABLE #NorTempTable
63 END
64GO
65 
66CREATE PROCEDURE dbo.MemoryOptTableTest --- Memory Optimized SQL temp table
67AS
68BEGIN
69SET NOCOUNT ON;
70 DECLARE @cnt INT = 0;
71 WHILE @cnt < 1000
72 BEGIN
73	INSERT INTO MemoryOptTempTable (First_Name,Last_Name) VALUES ('Sanya','J')
74	SET @cnt = @cnt + 1;
75 END
76 DELETE FROM MemoryOptTempTable;
77 END
78GO
79 
80CREATE PROCEDURE dbo.PhysicalTableTest --- Physical SQL temp table
81AS
82BEGIN
83SET NOCOUNT ON;
84 DECLARE @cnt INT = 0;
85 WHILE @cnt < 1000
86 BEGIN
87	INSERT INTO [PhysicalTempTable] (First_Name,Last_Name) VALUES ('Sanya','J')
88	SET @cnt = @cnt + 1;
89 END
90 TRUNCATE TABLE [PhysicalTempTable];
91 END
92GO
93 
94CREATE PROCEDURE dbo.VariableTable --- normal variable Table
95 AS
96 BEGIN
97SET NOCOUNT ON;
98    DECLARE @variableTab TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, [First_Name] 
99nvarchar(10) NULL,   [Last_Name] nvarchar(10) NULL) ;
100
101 DECLARE @cnt INT = 0;
102 WHILE @cnt < 1000
103 BEGIN
104	INSERT  @variableTab (First_Name,Last_Name) VALUES ('Sanya','J')
105	SET @cnt = @cnt + 1;
106 END
107
108END
109GO
110 
111CREATE PROCEDURE dbo.MemoryOptVarTable --- Memory Optimized variable Table
112 AS
113 BEGIN
114SET NOCOUNT ON;
115    DECLARE @MemOptVarTab MemoryOptTabVar ;
116
117	DECLARE @cnt INT = 0;
118	WHILE @cnt < 1000
119	BEGIN
120		INSERT @MemOptVarTab (First_Name,Last_Name) VALUES ('Sanya','J')
121		SET @cnt = @cnt + 1;
122	END
123    
124END
125GO