--https://www.sqlshack.com/using-memory-optimized-tables-to-replace-sql-server-temp-tables-and-table-variables/
DROP PROCEDURE IF EXISTS dbo.TempDB_TempTableTest;
DROP PROCEDURE IF EXISTS dbo.MemoryOptTableTest;
DROP PROCEDURE IF EXISTS dbo.PhysicalTableTest;
DROP PROCEDURE IF EXISTS dbo.VariableTable;
DROP PROCEDURE IF EXISTS dbo.MemoryOptVarTable;
GO

DROP TABLE IF EXISTS [MemoryOptTempTable];
DROP TABLE IF EXISTS [PhysicalTempTable];
DROP TYPE IF EXISTS MemoryOptTabVar;
GO

CREATE TABLE [MemoryOptTempTable]
(
 [ID] INT IDENTITY(1,1) NOT NULL ,
 [First_Name] nvarchar(10) NULL,
 [Last_Name] nvarchar(10) NULL,

 CONSTRAINT [PK_MemoryOptTempTable]  PRIMARY KEY NONCLUSTERED
(
 ID ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

CREATE TABLE [PhysicalTempTable]
(
 [ID] INT IDENTITY(1,1) NOT NULL ,
 [First_Name] nvarchar(10) NULL,
 [Last_Name] nvarchar(10) NULL,

 CONSTRAINT [PK_PhysicalTempTable]  PRIMARY KEY NONCLUSTERED
(
 ID ASC
)
)
GO

CREATE TYPE MemoryOptTabVar AS TABLE (
   [ID] INT IDENTITY(1,1) NOT NULL ,
   [First_Name] nvarchar(10) NULL,
   [Last_Name] nvarchar(10) NULL,
   INDEX IX_MemoryOptTabVar_ID NONCLUSTERED (ID)
) WITH (MEMORY_OPTIMIZED = ON)
GO

CREATE PROCEDURE dbo.TempDB_TempTableTest --- Normal SQL temp table
AS
BEGIN
SET NOCOUNT ON;
 CREATE TABLE #NorTempTable (ID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, [First_Name]
nvarchar(10) NULL,   [Last_Name] nvarchar(10) NULL)

 DECLARE @cnt INT = 0;
 WHILE @cnt < 1000
 BEGIN
    INSERT INTO #NorTempTable (First_Name,Last_Name) VALUES ('Sanya','J')
    SET @cnt = @cnt + 1;
 END

 DROP TABLE #NorTempTable
 END
GO

CREATE PROCEDURE dbo.MemoryOptTableTest --- Memory Optimized SQL temp table
AS
BEGIN
SET NOCOUNT ON;
 DECLARE @cnt INT = 0;
 WHILE @cnt < 1000
 BEGIN
    INSERT INTO MemoryOptTempTable (First_Name,Last_Name) VALUES ('Sanya','J')
    SET @cnt = @cnt + 1;
 END
 DELETE FROM MemoryOptTempTable;
 END
GO

CREATE PROCEDURE dbo.PhysicalTableTest --- Physical SQL temp table
AS
BEGIN
SET NOCOUNT ON;
 DECLARE @cnt INT = 0;
 WHILE @cnt < 1000
 BEGIN
    INSERT INTO [PhysicalTempTable] (First_Name,Last_Name) VALUES ('Sanya','J')
    SET @cnt = @cnt + 1;
 END
 TRUNCATE TABLE [PhysicalTempTable];
 END
GO

CREATE PROCEDURE dbo.VariableTable --- normal variable Table
 AS
 BEGIN
SET NOCOUNT ON;
    DECLARE @variableTab TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, [First_Name]
nvarchar(10) NULL,   [Last_Name] nvarchar(10) NULL) ;

 DECLARE @cnt INT = 0;
 WHILE @cnt < 1000
 BEGIN
    INSERT  @variableTab (First_Name,Last_Name) VALUES ('Sanya','J')
    SET @cnt = @cnt + 1;
 END

END
GO

CREATE PROCEDURE dbo.MemoryOptVarTable --- Memory Optimized variable Table
 AS
 BEGIN
SET NOCOUNT ON;
    DECLARE @MemOptVarTab MemoryOptTabVar ;

    DECLARE @cnt INT = 0;
    WHILE @cnt < 1000
    BEGIN
        INSERT @MemOptVarTab (First_Name,Last_Name) VALUES ('Sanya','J')
        SET @cnt = @cnt + 1;
    END

END
GO