Firstly, I am sure you are as excited as I am about the next version of SQL Server, and you can try the public preview (a.k.a. Community Technology Preview 2 - CTP2) by visiting the evaluation page and install it NOW! Today morning, at the start of a new week and I found something new in SQL 2016 CTP2 which has not been widely discussed as yet: inline index definition enhancements.
As a regulatory disclaimer though I must mention that the information here may be subject to potential change in future releases beyond CTP2.
SQL Server 2014 introduced T-SQL grammar support allowing us to declare index definitions inline with the CREATE TABLE itself. While this was mainly driven by the semantics of the in-memory OLTP tables, the elegance of defining indexes along with the table is compelling. However there were some drawbacks:
- Inline definition of columnstore indexes was not allowed.
- Inline indexes could not have a filter predicate associated.
I’m glad to see that both of these have scenarios have been addressed in SQL Server 2016 CTP2. Here are some examples of the new syntax which work in SQL Server 2016 CTP2:
Inline Filtered Index
create table t1
index IX1 (c1) where c1 > 0
Inline Columnstore Index
-- inline clustered columnstore index definition
create table t2
index CCSI1 clustered columnstore
-- inline nonclustered columnstore index definition
create table t3
index NCCSI1 nonclustered columnstore (c1, c2)
The CREATE TABLE help in SQL 2016 CTP2 Books Online has been updated to reflect these enhancements.
I’ll be posting other such nuggets on an ongoing basis, so stay tuned!