SQL Server 2014 – Index Enhancements

Inline Specification of Clustered and NON-Clustered Indexes

SQL Server 2014 is launched and there are many new things added in the product. DBAs were waiting for a very long time for some of those. Creating Inline Clustered and NON-Clustered Indexes is one of them.

If you are familiar with T-SQL coding, you should know that you cannot add NON clustered index while creating a table in CREATE TABLE statement. The NON-Clustered indexes were to be added after the creation of the table. Now you can add Non-Clustered indexes in the CREATE TABLE statement.

Some of you say “What’s the PLUS point in this?” Well, I have myself witnessed that when you are creating the database tables, you are more involved in the design. Generally, you have all the information regarding the data that the table is going to hold and the major user queries that are going to be executed on that table. Point is, that is the best time to decide to add indexes. You generally do this. But at times, you forget to exclusively add and run the “CREATE INDEX” statement. This results in an un-wanted delays and, sometimes, results in the crash, especially when you are reading data for reports from the tables on which you forgot to add an index. With the ability of creating Inline Indexes, the chances of such mistakes is even lower now.

If you execute the following in SQL Server 2012, it will generate an error;

Use AdventureWorks2012

Go

CREATE TABLE tblTest

(

       ID int IDENTITY (1,1) PRIMARY KEY,

       NAME nvarchar(100),

       FATHERNAME nvarchar(100) index idx_FATHERNAME

)

The error will be;

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

But if you execute the same code in SQL Server 2014 (any edition), the command will be executed successfully.

You can also create composite indexes in the same manner. Included columns and filtered conditions are not yet supported with Inline specification of indexes.

Use AdventureWorks2012

Go

CREATE TABLE tblTest

(

       ID int IDENTITY (1,1) PRIMARY KEY,

       NAME nvarchar(100),

       FATHERNAME nvarchar(100) index idx_FATHERNAME (FATHERNAME,NAME)

)

Table variable can have an index

Yes, now we can have an index on a table variable defined in the T-SQL code. We all know the importance of the table variable. Here is the example code;

Declare @vTabletest TABLE 

(

       ID int IDENTITY (1,1) PRIMARY KEY,

       NAME nvarchar(100),

       FATHERNAME nvarchar(100) index idx_FATHERNAME

)

Insert into @vTabletest values ('A','B')

Select * from @vTabletest

Again, if you try to execute the above code in SQL Server 2012, it will generate errors.

You can also create composite indexes in the same manner. Included columns and filtered conditions are not yet supported with Inline specification of indexes.

Clustered Columnstore Indexes

SQL Server 2014 enables you to create a clustered columnstore index. In the prior versions, only non-clustered columnstore indexes were offered.

In order to create a clustered columnstore index on a table, first you will have to create a table without any index on it i.e. a heap;

CREATE TABLE tblTest 

(

       ID int ,

       NAME nvarchar(100),

       FATHERNAME nvarchar(100)

)

GO

Now, execute the command to create a column stored Index;

CREATE CLUSTERED COLUMNSTORE INDEX idx_CS_Test on tbltest

The above command converts the table into the clustered columns store index named idx_CS_test. As you can see in the above statement, you cannot specify the key list in the index specification as we used to do in the non-clustered columnstore indexes. This is because the whole table is converted into the columnstore index.