Non Unique Clustered and Non Clustered Indexes For Table Variables in SQL Server 2014

Introduction

Another new feature, support of Non-Unique Clustered and Non-Clustered Indexes for Table Variables, has been introduced in SQL Server 2014.

So I am try to implement this awesome new feature. Table Variables are nice, as a result you are able to avoid excessive recompilations with them. They have no statistics, and once you make them, you are not altering the database schema. They are simple variables, however still persisted in the TempDb. One disadvantage is that you are just unable to make Non-Clustered Indexes on them, which is dangerous after managing a larger dataset. With SQL Server 2014 CTP1 however that behavior is currently modified.

See the code given below.

Example

DECLARE @temptest TABLE

(

uID INT IDENTITY(1, 1) PRIMARY KEY,

FName Varchar(100) INDEX idx_FirstName,

LName Varchar(100)

)

 

INSERT INTO @temptest (FName, LName)

SELECT TOP 1000 name, name FROM master.dbo.syscolumns

 

 

SELECT FName FROM @temptest

WHERE FName = 'cid'

GO

Output


When you gaze on the Execution Plan of the SELECT Statement, SQL Server is executing a Non-Clustered Index Seek operator. As you'll be see you're currently able to produce additional Non-Clustered indexes on the Table Variable.
Each created Non-Clustered Index has no Statistics Object adhered to it. It's an awfully nice, straightforward syntax that is additionally supported on "normal" database tables that you simply create.

Example

CREATE TABLE Test

(

Column1 INT PRIMARY KEY CLUSTERED,

Column2 INT INDEX idx_Col2,

Column3 INT INDEX idx_Col3

)

GO
 

See the index Description

Furthermore it's also likely to make composite indexes with the new syntax. See the following table.

CREATE TABLE Test1

(

Column1 INT PRIMARY KEY CLUSTERED,

Column2 INT INDEX idx_Col2 (Column2, Column3),

Column3 INT

)

GO

See the index Description



ref-http://www.sqlpassion.at/