Problem

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?

Solution

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) 
)

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)
);

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

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.
Variable2014-1.PNG

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_ELEVATE_TO_SNAPSHOT:

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;

 

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)

 

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

Variable2014-2.PNG

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s