Performance Improvements in SQL 2014(2/4)

Haja Batcha

Hi dears, on my previous blog we saw about a enhancement regarding a update statistics on SQL 2014. Will see yet another enhancement on SQL Server 2014 which is non clustered indexes on temporary table, and which can also be called inline indexes.

“Non clustered indexes on table variable”

First we will see ‘Which is not possible in previous edition, which is now possible in 2014′ secondly we will see ‘Which is possible in previous edition, which can be coded very easily’  

The table variables are one the wonderful feature which was introduced on SQL Server 2000. Because with table variable excessive recompilation will be avoided, it doesn’t need statistics when you create them. And the one big disadvantage was, you cannot create non clustered index on them, that too resolved from SQL Server 2014 CTP1 .

With SQL Server 2014 CTP1 , now it is possible to create Non Clustered Index on table variable.

Now we will see a simple demo regarding this enhancement

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

Just execute the above query and see the execution plan, you can see the index seek operation on the FirstName which uses the Index idx_FirstName. Its just awesome, don’t miss to notice one more thing, you can create your indexes inline just like defining a data type.

The feature we saw above was not exist with previous version, hence you should have your database compatibility level least to 110.  Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.

So for we saw a feature which is new to SQL Server 2014, And another feature which exist already can code easily is you can create the index inline to the normol tables as well, instead of creating the index with CREATE INDEX statement as like below

CREATE TABLE Employee

(

ID INT IDENTITY(1, 1) PRIMARY KEY,

FirstName CHAR(100) INDEX idx_FirstName,

LastName CHAR(100) INDEX idx_LastName

)

Now how simply you create the indexes on inline. Furthermore it’s also possible to create composite indexes with the new syntax

view source

print?

CREATE TABLE Employee

(

ID INT IDENTITY(1, 1) PRIMARY KEY,

FirstName CHAR(100) INDEX idx_Name(FirstName,LastName),

LastName CHAR(100)

)