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.
