Archive
Memory Optimized “Table Variables” in SQL Server 2014 and 2016
SQL Server 2014 provided you these new features to create [Memory Optimized Tables] and [Native Compiled Stored Procedures] for efficient and quick processing of data and queries which happens all in memory.
It also provided you one more feature to create Memory Optimized Table Variables, in addition to normal Disk Based Table Variables.
This new feature would provide you more efficiency in Storing, Retrieving and Querying temporary data from and in memory.
Normal Table Variables are created in tempdb and utilize it for their entire life. Now with these new Memory Optimized Table Variables they will become free from tempdb usage, relieve tempdb contention and reside in memory only till the scope i.e. batch of a SQL script or a Stored Procedure.
Let’s see how to use these and what performance gain you get out of these tables.
–> Enable Database for supporting Memory Optimized tables: To use this feature your Database should be associated with a FileGroup. So, let’s alter the database.
USE [TestManDB]
GO
-- Add the Database to a new FileGroup
ALTER DATABASE [TestManDB]
ADD FILEGROUP [TestManFG] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [TestManDB]
ADD FILE (
NAME = TestManDBFG_file1,
FILENAME = N'E:\MSSQL\DATA\TestManDBFG_file1' -- Put correct path here
)
TO FILEGROUP TestManFG
GO
Otherwise, while creating Memory Optimized objects you will get below error:
Msg 41337, Level 16, State 100, Line 1
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.
You cannot create a Memory Optimized Table Variable directly with DECLARE @TableVarName AS TABLE (…) statement. First you will need to create a Table Type, then based upon this you can create Tables Variables.
–> Create a Table TYPE [Person_in_mem]
CREATE TYPE dbo.Person_in_mem AS TABLE( BusinessEntityID INT NOT NULL ,FirstName NVARCHAR(50) NOT NULL ,LastName NVARCHAR(50) NOT NULL INDEX [IX_BusinessEntityID] HASH (BusinessEntityID) WITH ( BUCKET_COUNT = 2000) ) WITH ( MEMORY_OPTIMIZED = ON ) GO
The Memory Optimized Table Type should have an Index, otherwise you will see an error as mentioned below:
Msg 41327, Level 16, State 7, Line 27
The memory optimized table ‘Person_in_mem’ must have at least one index or a primary key.
Msg 1750, Level 16, State 0, Line 27
Could not create constraint or index. See previous errors.
Ok, now as we’ve created this table type, now we can create as many Table Variables based upon this.
–> Now, create a Table variable @PersonInMem of type [Person_in_mem] that is created above:
DECLARE @PersonInMem AS Person_in_mem -- insert some rows into this In-Memory Table Variable INSERT INTO @PersonInMem SELECT TOP 1000 [BusinessEntityID] ,[FirstName] ,[LastName] FROM [AdventureWorks2014].[Person].[Person] SELECT * FROM @PersonInMem GO
Here we successfully created a Table Variable, inserted records into it and retrieved same by the SELECT statement, and this all happened in memory.
Now how can we see we that how much benefits we got from this? What we can do is, we can create a separate Disk-Based Table Variable and do similar operation on it and compare the results by checking the Execution Plan.
–> Comparing performance of both In-Memory vs Disk-Based Table-Variables
– Enable the Actual Execution Plan and run below script to Create and Populate both:
1. In-Memory Table Variable
2, Disk-Based Table Variable
-- 1. In-Memory Table Variable DECLARE @PersonInMem AS Person_in_mem INSERT INTO @PersonInMem SELECT TOP 1000 [BusinessEntityID] ,[FirstName] ,[LastName] FROM [AdventureWorks2014].[Person].[Person] select * from @PersonInMem -- 2. Disk-Based Table Variable DECLARE @Person AS TABLE ( BusinessEntityID INT NOT NULL ,FirstName NVARCHAR(50) NOT NULL ,LastName NVARCHAR(50) NOT NULL ) INSERT INTO @Person SELECT TOP 1000 [BusinessEntityID] ,[FirstName] ,[LastName] FROM [AdventureWorks2014].[Person].[Person] select * from @Person GO
–> Now, check the Actual Execution Plan results below:
1. Check the Cost of INSERT operation with both the tables:
– It took only 8% cost to insert into In-memory Table Variable.
– But it took 89% cost to insert into a Disk-Based Table Variable.
> If You see the individual Operators in both the plans you will see that :
For @PersonInMem Table Variable the cost of INSERT was just 19% compared to the cost of INSERT for @Person Table Variable that was 92%.
2. Check the Cost to SELECT/Retrieve rows both the tables:
– It took only 0% cost to retrieve rows from the In-memory Table Variable
– And it took 3% cost to retrieve rows from a Disk-Based Table Variable
This proves that the INSERT and SELECT operations with Memory Optimized table are way more faster that normal Disk-Based tables.
Thus, using Memory Optimized Table Variables will provide you better performance for storing temporary data within memory and process with in Stored Procedure or your T-SQL Scripts.
Update: Know more about In-Memory tables:
Difference between Temporary Table and Table Variable, which one is better in performance? – MSDN TSQL forum
–> Question:
Anyone could you explain What is difference between Temp Table (#, ##) and Table Variable (DECLARE @V TABLE (EMP_ID INT)) ?
Which one is recommended to use for better performance?
Also is it possible to create CLUSTER and NONCLUSTER Index on Table Variables?
In my case: 1-2 days transnational data are more than 3-4 Millions. I tried using both # and table variable and found table variable is faster.
Is that Table variable using Memory or Disk space?
–> My Answer:
Check this link to see differences b/w Temp Table & Table Variable.
TempTables & TableVariables both use memory & tempDB in similar manner, check this blog post.
Performance wise if you are dealing with millions of records then Temp Table is ideal, as you can create explicit indexes on top of them. But if there are less records then Table Variables are good suited.
On Tables Variable explicit index are not allowed, if you define a PK column, then a Clustered Index will be created automatically.
Ref Link.
Table variables are not stored in memory, but in tempdb
Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.
–> To prove this I executed following code:
-- Batch #1 SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #2 CREATE TABLE #tempTab (j INT) INSERT INTO #tempTab VALUES (1) SELECT * FROM #tempTab SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO -- Batch #3 DECLARE @tabVar TABLE (i INT) INSERT INTO @tabVar VALUES (1) SELECT * FROM @tabVar SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES GO DROP TABLE #tempTab GO
Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.
Batch #2: The second batch of stmt gives me 1 row with following table name:
#tempTab_____________________________________________________________000000019D39
Batch #3: and the third batch of stmt gives me 2 rows with following table name:
#tempTab_____________________________________________________________000000019D39 #0C4F413A
This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.
–> Check the full demo here:
SQL basics – Temporary Tables vs Table Variables
I see lot of people debating on usage of Temporary Tables & Table Variables. And everyone cites their own definition and examples, and most of them conflicts with each other. So I thought to put the differences & points that are valid and tested:
–> Temporary Tables:
1. Syntax: CREATE TABLE #T (..)
2. A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited to 116 chars.
3. The Scope of this Temp-Table is limited to its session, like a Stored Procedure, or a set of nested Stored Procedures.
4. The Temp-Table gets Dropped automatically when the session ends or the Stored Procedure execution ends or goes out of scope.
5. One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved.
6. Global Temporary Tables (##) operate much like Local Temporary Tables; they are also created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all the sessions, until the creating session goes out of scope.
7. One can create desired Indexes on Temporary Tables (like permanent tables) and these make use of Statistics, thus resulting in better query plan compared to Table variables.
–> Table Variables:
1. Syntax: DECLARE @T TABLE (…)
2. A Table Variable is also created on disk in the tempDB system database. But the name of this Table Variable is generated completely by the SQL engine and it also differs with other similar named tables created in same or other sessions.
3. The Scope of Table Variables is limited to its BATCH only like other variables. Contrary to the temporary tables, they are not visible in nested stored procedures and in EXEC(@SQLstring) statements.
4. The Table Variable gets Dropped automatically when the BATCH ends (after the GO batch separator) or the Stored Procedure or Function execution ends or goes out of scope.
5. A Table Variable is created in memory, this is a myth. They are also treated as Temp-Tables and created in tempdb, but they performs slightly better than Temp-Tables because there is even less locking and logging in a Table Variable.
6. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a UDF (User Defined Function). You can create a Table Variable within a UDF, and modify the data using one of the DML statements, this is not possible with Temp-Tables.
7. A Table Variable will always have a cardinality of 1, thus statistics are not tracked for them and may result in bad query plan.
–> Limitations with Table variables:
8. Table Variables do not participate in TRANSACTIONS and locking.
9. You cannot use a Table Variable in either of the following situations:
a. INSERT @table EXEC spSomeProcedure (Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.)
b. SELECT * INTO @table FROM someTable
10. You cannot Truncate a Table Variable.
11. Table Variables cannot be Altered after they have been declared.
12. You cannot explicitly add an index to a Table Variable, however you can create an inline index through a PRIMARY KEY CONSTRAINT, and multiple indexes via UNIQUE CONSTRAINTs.
13. You cannot create a named Constraint on Table Variables. You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column or DEFAULT CONSTRAINT.
14. You cannot use a user-defined type (UDT) in a column definition.
15. Unlike a #temp table, you cannot DROP a Table Variable when it is no longer necessary, you just need to let it go out of scope.
16. You can’t build the Table Variable inside Dynamic SQL. This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.
17. Table variables are not visible to the calling procedure in the case of nested Stored Procs. It is possible with temp tables.
18. You cannot insert explicit values into an IDENTITY column of a Table variable (the Table Variables does not support the SET IDENTITY_INSERT ON).
–> Now the question is when to use either of them?
– Temporary Tables: When you are dealing with large volume of data sets use Temp-Tables, as you can create Indexes on them and they use Statistics for accurate cardinality estimations, thus providing a better query plan.
– Table Variables: When you are dealing with smaller data sets, use Table Variables, as they would not acquire locks and are Transaction free, and may not be affected by the absence of Indexes and Stats.
–> Check the full demo here:
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 