Clustered index creation fail on table creation

Question

We've got an error when running the following script;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Table_Name')
BEGIN
CREATE TABLE Table_Name
(
    Field_Name_1 binary(32) NOT NULL CONSTRAINT PK_Name_Goes_Here PRIMARY KEY NONCLUSTERED
    , Field_Name_2 int NOT NULL 
    , Field_Name_3 datetime NOT NULL INDEX IX_Name_Goes_Here CLUSTERED
)
END 

In particular it's the clustered index creation throwing the following error:

Msg 1018, Level 15, State 1, Line 15
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.

This is odd as it works on all of our servers except for a particular QA server. The fix we've put in place is to create the clustered index outside of the table creation statement but I'd be interested if anybody has come across this problem before?

Answer 1

The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016):

The other instances, where this syntax is working for you, must be on SQL Server 2014 or newer.

On 2012, regardless of compatibility level, you'll need to create the index separately.

Answer 2

I'd have to run it through SSMS to know for sure, but that syntax looks off to me. While I've defined columns as primary keys inline (like you are doing) I've never attempted to define a clustered index that was not the primary key in that fashion. What you are doing simply isn't possible in the SQL Server version you are using. I think you should define the clustered index using a standard CREATE INDEX after the table has been created via the CREATE TABLE statement.