unique index syntax

  • The following syntax works on 2017 SQL Server, but fails on 2014:

    create table TESTUNQ
      COL1 int not null
    , COL2 int not null

    , index UIQ_COL1_COL2 unique nonclustered (COL1,COL2)


    I am getting: Incorrect syntax near the keyword 'unique'

    I understand that I could use this syntax:

    create unique index UQ_TESTUNQ on TESTUNQ (COL1,COL2);

    and it works on 2014, but I need to know the reason for the original syntax error, the MS docs for 2014 shows the syntax to be perfectly valid  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-2014

    select @@VERSION shows:

    Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64) Jul 20 2019 21:42:29 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    Any pointers would be appreciated


  • Hi migurus,

    Your t-sql statement works fine on 13.0.5698.0.

    May be the index name is allocated to another object in that database?

  • No, the name is unique, I have checked that

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply