unique index syntax

• migurus

  SSC-Addicted

  Points: 456

  More actions

  June 28, 2020 at 12:56 am

  #3765362

  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

   

• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  June 28, 2020 at 9:02 pm

  #3765383

  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?

• migurus

  SSC-Addicted

  Points: 456

  More actions

  June 29, 2020 at 3:43 pm

  #3766205

  No, the name is unique, I have checked that