10

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?

11

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):

enter image description here

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.

-9

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.

  • 2
    I thought it looked odd too when I first saw it but it works just fine on all of our other boxes, including my dev boxes (up to vnext). And creates both indexes just fine. – Rich Benner Feb 21 '17 at 11:57
  • Perhaps then it is tied to the SQL Server version running on the server, and/or the SQL compatibility mode of the database? SQL Server has added a lot of 'syntactic sugar' over the years. If you go back far enough you can't even declare a variable and set it in the same line. – Matthew Sontum Feb 21 '17 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.