Earlier today I have seen a Tweet from Paul White, where he tweeted that SQL Server 2014 supports Non-Unique Clustered and Non-Clustered Indexes for Table Variables in SQL Server 2014. So I had to start up my virtual machine and try it out, because this would be a awesome new feature. Table Variables are great, because you can avoid excessive recompilations with them. They have no statistics, and when you are creating them, you are not changing the database schema. They are just variables, but still persisted in TempDb.

One drawback is that you where not able to create Non-Clustered Indexes on them, which is bad when you are dealing with a larger dataset. But with SQL Server 2014 CTP1 that behavior is now changed. Just have a look on the following code:

DECLARE @tempTable TABLE
(
   ID INT IDENTITY(1, 1) PRIMARY KEY,
   FirstName CHAR(100) INDEX idx_FirstName,
   LastName CHAR(100)
)
	
INSERT INTO @TempTable (FirstName, LastName)
SELECT TOP 100000 name, name FROM master.dbo.syscolumns
	
SELECT FirstName FROM @TempTable
WHERE FirstName = 'cid'
GO

When you look on the Execution Plan of the SELECT Statement, SQL Server is executing a Non-Clustered Index Seek operator. As you can see you are now able to create additional Non-Clustered indexes on the Table Variable. Each created Non-Clustered Index has no Statistics Object attached to it. It’s a very nice, easy syntax which is also supported on “normal” database tables that you create. Let’s have a look on the following table definition:

CREATE TABLE foo
(
	Col1 INT PRIMARY KEY CLUSTERED,
	Col2 INT INDEX idx_Col2,
	Col3 INT INDEX idx_Col3
)
GO

Furthermore it’s also possible to create composite indexes with the new syntax:

-- Inline creation of Indexes
CREATE TABLE foo
(
	Col1 INT PRIMARY KEY CLUSTERED,
	Col2 INT INDEX idx_Col2 (Col2, Col3),
	Col3 INT
)
GO

Very nice 🙂

Thanks for reading

-Klaus