SQL Server 2014 (download CTP 2) has new T-SQL features. For example you can create inline non-unique indexes. Till SQL Server 2014 the only option to create inline indexes was to define PRIMARY KEY [NON]CLUSTERED or UNIQUE [NON]CLUSTERED constraints because SQL Server maintains these primary key/unique constraints by creating unique indexes (see section “PRIMARY KEY or UNIQUE constraint” from Create Unique Indexes).
Example:
Code Snippet
CREATE TABLE dbo.SalesOrder
(
SalesOrderID INT IDENTITY(1,1),
– Old method to create an CLUSTERED unique index
CONSTRAINT PK_SalesOrder_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID),
OrderNumber CHAR(8) NOT NULL,
– Old method to create an NON-CLUSTERED unique index
CONSTRAINT UN_SalesOrder_OrderNumber UNIQUE(OrderNumber),
– [New] This will create a non-unique index on (OrderDate)
OrderDate DATE NOT NULL INDEX IX_SalesOrder_1 NONCLUSTERED ,
CustomerID INT NOT NULL,
– [New] This will create a non-unique index on (CustomerID, OrderDate)
INDEX IX_SalesOrder_2 NONCLUSTERED (CustomerID, OrderDate)
);
(
SalesOrderID INT IDENTITY(1,1),
– Old method to create an CLUSTERED unique index
CONSTRAINT PK_SalesOrder_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID),
OrderNumber CHAR(8) NOT NULL,
– Old method to create an NON-CLUSTERED unique index
CONSTRAINT UN_SalesOrder_OrderNumber UNIQUE(OrderNumber),
– [New] This will create a non-unique index on (OrderDate)
OrderDate DATE NOT NULL INDEX IX_SalesOrder_1 NONCLUSTERED ,
CustomerID INT NOT NULL,
– [New] This will create a non-unique index on (CustomerID, OrderDate)
INDEX IX_SalesOrder_2 NONCLUSTERED (CustomerID, OrderDate)
);
