Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.

Non-Clustered Indexes on Table Variables in SQL Server 2014

(Be sure to checkout the SQLpassion Online Academy, where you get High-Quality SQL Server Trainings with Instant Access!)

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

Comments

Leave a comment on the original post [www.sqlpassion.at, opens in a new window]

Loading comments...