What are new features for a variable table in SQL Server 2014

In my previous article, I talked about the differences between a temporary table and a variable table in SQL 2008, but what are their pros and cons? In previous SQL versions the variable table has been used popularly by SQL developers and DBAs but it has many disadvantages while working on temporarily huge data. What is happening to the variable table in SQL Server 2014? Has Microsoft made some updates to improve performance of the variable table? Let's investigate.

Microsoft has announced a new feature called INLINE INDEX CREATION in SQL Server 2014 CTP1 and we can definitely apply this feature for the variable table.

USE Database_Test
                      GO
                      DECLARE @Address TABLE
                      (
                      AddressID int,
                      City nvarchar(30),
                      StateProvinceID int,
                      AddressLine1 nvarchar(60),
                      INDEX cl_AddressID CLUSTERED (AddressID),
                      INDEX ix_StateProvinceID NONCLUSTERED (StateProvinceID) 
                      )
                      

Select all Open in new window

One of things that surprised me was that Microsoft supports FILLFACTOR. However, it's not 100 per cent certain you can create every kind of index which you want. For example, you can not include columns or apply a filter to an inline index on the variable table. This is the opposite of the temporary table, when you can do that after creating the table. Or can you?

DECLARE @Inline_FillFactor TABLE
                      (
                      ID int PRIMARY KEY,
                      ColumnA nvarchar(30),
                      ColumnB nvarchar(3),
                      INDEX ix_Inline_FillFactor NONCLUSTERED(ColumnA) WITH(FILLFACTOR = 95)
                      );
                      

Select all Open in new window

Well...! This is a change for the variable table, so one question pops up:  IS A VARIABLE TABLE AS GOOD AS A TEMPORARY TABLE? One of the most important impacts to performance is STATISTICS and  we are able to create inline indexes on the variable table so  we're expecting it allows some populated statistics on the variable table.

GO
                      DECLARE @Address TABLE
                      (
                      AddressID int,
                      City nvarchar(30),
                      StateProvinceID int,
                      AddressLine1 nvarchar(60),
                      INDEX cl_AddressID CLUSTERED (AddressID),
                      INDEX ix_StateProvinceID NONCLUSTERED (StateProvinceID) 
                      )
                      
                      INSERT @Address(AddressID,City,StateProvinceID,AddressLine1)
                      SELECT AddressID,City,StateProvinceID,AddressLine1 FROM AdventureWork2012.[Person].[Address]
                      
                      SET STATISTICS PROFILE ON
                      
                      SELECT City, AddressLine1
                      FROM @Address
                      WHERE StateProvinceID = 79
                      
                      SET STATISTICS PROFILE OFF
                      

Select all Open in new window

Here is result of STATISTICS. SQL Engine did a scan operator on clustered index though we created a non-clustered index on StateProvinceID and Estimate Rows is 1 instead of 2636.
I do not undestand why Microsoft allows us to create indexes but not populated statistics in the variable table. SQL optimizier is based on many factors to determine a correct execution plan and one of them is STATISTICS information. If STATISTICS is incorrect, the SQL Optimizier can determine an incorrect execution plan and impact performance.  

If you're lucky and have a chance to work on SQL 2014 Enterprise Edition, you can apply IN-MEMORY OPTIMIZED OLTP feature on a variable table. In this article, I will introduce the way to apply this feature on a varible table.

First, make some changes to your database. Create a new filegroup to apply IN-MEMORY, then set the database to MEMORY_OPTIMIZED_ELEVAT E_TO_SNAPS HOT:

USE master;
                      GO
                      ALTER DATABASE Database_Test ADD FILEGROUP [MemoryOptmizedFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA;
                      GO
                      ALTER DATABASE Database_Test ADD FILE(name='NewFileGroup1',filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\NewFileGroup1') 
                      TO FILEGROUP [MemoryOptmizedFileGroup]
                      GO
                      ALTER DATABASE Database_Test SET  MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT  ON;
                      

Select all Open in new window

We can start a memory optmizied variable table and the first step is to create a table type which is the same with variable table's definition:

USE Database_Test
                      GO
                      CREATE TYPE Address AS TABLE
                      (
                      AddressID int not null,
                      City nvarchar(30) not null,
                      StateProvinceID int not null,
                      AddressLine1 nvarchar(60),
                      INDEX ix_StateProvinceID NONCLUSTERED (StateProvinceID) 
                      ) WITH (MEMORY_OPTIMIZED =ON) 
                      

Select all Open in new window

-Next, we will populate data and query it to check STATISTICS information:

USE Database_Test
                      GO
                      DECLARE @Address Address;
                      
                      INSERT @Address(AddressID,City,StateProvinceID,AddressLine1)
                      SELECT AddressID,City,StateProvinceID,AddressLine1 FROM [Person].[Address]
                      
                      SET STATISTICS PROFILE ON
                      
                      SELECT City, AddressLine1
                      FROM @Address
                      WHERE StateProvinceID = 79
                      
                      SET STATISTICS PROFILE OFF
                      

Select all Open in new window

We can see that SQL engine did a SEEK index on the nonclustered index instead of a SCAN index on the clustered index although value of Estimated Rows is still 1. The SEEK index is better than a SCAN index in this case.
 

Conclusion

Although Microsoft suppports non-clustered indexes on a variable table, the STATISTICS information has not been associated with the indexes. So, a temporary table is still always the better choice for storing huge volume data at the moment; we will have to wait for improvements to the variable table if Microsoft keeps using variable tables along with temporary tables.

Option 1: Inline indexes on a temporary table. This works on Standard Edition, instead of creating non-clustered indexes after the temp table exists, you can improve caching of a temporary table by creating non-clustered indexes with the temporary table. 

Option 2: In-memory optimized on a variable table. This only works on Enterprise Edition and you must test this carefully because our queries and store procedures are always complex and we get unexpected execution plans.