Today, we will quickly see how do we create memory optimized temporary table in SQL Server and how to use it efficiently.

I would recommend to read previous blogs before we continue further:
In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014
In-Memory OLTP: Compatibility Check for Datatypes in SQL Server
In-Memory OLTP: Compatibility Check for SQL Server and Database

Before we start the technical aspect, let us be clear why do we need memory optimized temp tables in SQL Server. Temp tables are very powerful objects in SQL server which is created in TEMPDB. These tables are, as name mentioned, created for a short live may be a for a batch scope, procedure scope etc. With in the scope, we can use the temp tables multiple times. So, many developers would use these objects to populate the data as required for thier operations and use these objects instead of querying the original physical table. Not to wonder, now days, the usage of temp tables are more and many is thinking its a free tool 🙂 .

But, on flip side, as we create huge number of tables, there is also a performance imapct in the system. There is only one database “TempDB” per instance where SQL Server would need to create these temp objects and do the oprations. So, it is observed there are some serious performance issues with tempDB at times. I am not covering tempdb performance issuesin this blog, but introduce memory optimized temp tables introduced in SQL Server 2014 which we can efficiently replace with normal temp objects to avoid performance issues.

Lets get into Techinical now –

Here is a sample example of creating a physical table and a procedure.

Create Table T1 (PeopleID int Primary key, CourseID int)

Insert into T1 values(1,100),(2,100),(3,200),(4,200),(5,200),(6,300)
GO
Create procedure usp_Normalproc @CourseID int
As
Begin
	
	Create Table #TempTable(PeopleID int, CourseID int)

	Insert into #TempTable (PeopleID,CourseID)
		Select PeopleID,CourseID From T1 where CourseID=@CourseID
	
	Select * From #TempTable
End

Exec usp_Normalproc 100

Now, lets look at how we convert this temp table usage to memory optimized temp object in the procedure.

First, Lets create a memory optimized table as below:

Create Table TempTable(PeopleID int, CourseID int,
SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  
INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
 CONSTRAINT CHK_TempTable_SpidFilter  CHECK ( SpidFilter = @@spid ),)
 WITH  
        (MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_ONLY);

SpidFilter – is a new column introduced as part of conversion to identify the rows for the session created. This has been indexed for performance.
CHK_TempTable_SpidFilter – This a constraint to make sure the spid has been populated

As next step, We need to create a function as below which is used to filter data for session.

CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid; 

Finally, we will create a security policy to filter the records from the temptable as per the session id its being used.

CREATE SECURITY POLICY dbo.TempTable_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.TempTable  
    WITH (STATE = ON);  

Once we set up memory optimzed table and filters, its time to change the procedure as below:

Alter procedure usp_MO_proc @CourseID int
As
Begin
	
	--Create Table #TempTable(PeopleID int, CourseID int)

	Insert into TempTable (PeopleID,CourseID)
		Select PeopleID,CourseID From T1 where CourseID=@CourseID
	
	Select * From TempTable

	Delete From TempTable 
End

It is very important to delete the data from the memory optimized table. On failing, this will create multiple records into memory optimized table if we re-run the procedure within the same session(please note that its very likely during testing).

Hope you enjoyed this blog, looking for your valuable feedback on the same.