There are a lot of features of SQL Server that I (and I know some of you), never really get around to using, usually because you have your ways, you have done things the same way for 100 years, and it all works fine enough. You may have heard of a feature, but said I will get to it someday. Inline index definitions are one such feature for me. I had used them a few times when setting up in-memory OLTP demos, but not enough to ingrain them in my mind. And I have 20 year experience typing the CREATE INDEX statement, and the syntax for adding an index inline to the declaration is just different enough to require looking up to use. So why bother.

A few days ago, I was creating a new table, and I bothered. I wish I had taken the time earlier, as this syntax makes creating a table a lot cleaner and easier. It was added to SQL Server 2014 to enable in-memory OLTP table creation. In 2014, you could not add or drop indexes from in-memory tables, so they all had to be created at table create time. However, the syntax also worked for on-disk tables, which my example statement that follows will show. Note that SQL Server SP2 extended inline index definition to filtered and columnstore indexes as well.

The CREATE TABLE statement that follows gives several examples of the syntax, along with a couple of constraints as well:

USE tempdb;
GO

DROP TABLE IF EXISTS Sales.Account;
DROP SCHEMA IF EXISTS Sales;
GO

CREATE SCHEMA Sales;
GO

CREATE TABLE Sales.Account
(
  –inline PK constraint
  AccountId int NOT NULL CONSTRAINT PKAccount PRIMARY KEY,

  –inline Unique constraint
  AccountNumber char(8) NOT NULL CONSTRAINT AKAccount_AccountNumber UNIQUE,

  –inline index on AccountName
  AccountName nvarchar(100) NOT NULL INDEX XAccountName,

  –2016SP2 index on account status, filtered
  AccountStatus char(10) NOT NULL INDEX XActiveAccounts WHERE AccountStatus = ‘Active’,

  –composite index, and you can use index settings, including UNIQUE
  INDEX XNumberAndName UNIQUE(AccountNumber, AccountName)
                                                                            WITH (ALLOW_PAGE_LOCKS = ON),

   –this would make this the clustered index
   –INDEX XNumber UNIQUE CLUSTERED (AccountNumber),

   INDEX XCColumnstore CLUSTERED COLUMNSTORE –2016SP2 makes the PK nonclustered too

   –INDEX NCColumnstore NONCLUSTERED COLUMNSTORE (AccountId, AccountNumber) –2016SP2

);
GO

This can save you quite a bit of typing when you are creating a new table (especially one with a lot of indexes), though it doesn’t really save any time creating the tables or indexes (other than not creating the table if you have an issue with the syntax.) In the future, I will likely create my tables using this syntax when typing. Since there really is not performance gain, if I am generating a script of a table, I expect that it will be in quite a few statements instead of this one, much like I would do with all of the constraint types.