Duplicate Index

• GBeezy

  SSCarpal Tunnel

  Points: 4723

  More actions

  November 30, 2017 at 10:15 am

  #324871

  If a developer creates the following index:

  BEGIN
      IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[CLDO_Queue]') AND [name] = 'PK_CLDO_Queue_MemberID_QID')
      BEGIN
          ALTER TABLE [dbo].[CLDO_Queue] ADD CONSTRAINT [PK_CLDO_Queue_MemberID_QID_cl] PRIMARY KEY CLUSTERED (MemberID, QID) WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
      END
  END
  GO

  Is there a need for the following index:

  BEGIN
      IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[CLDO_Queue]') AND [name] = 'IX_CLDO_Queue_QID')
      BEGIN
          CREATE UNIQUE NONCLUSTERED INDEX [IX_CLDO_Queue_QID] ON
              [dbo].[CLDO_Queue](QID) WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
      END
  END
  GO

  Thank You

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  November 30, 2017 at 10:24 am

  #1970095

  Yes those are different indexes(ignoring that one will be clustered and one is non clustered) and can potentially be used differently by the optimizer.  Also the second index has a unique constraint on it which would not be enforced by the first index.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 30, 2017 at 10:28 am

  #1970096

  GBeezy - Thursday, November 30, 2017 10:15 AM

  If a developer creates the following index:

  BEGIN
      IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[CLDO_Queue]') AND [name] = 'PK_CLDO_Queue_MemberID_QID')
      BEGIN
          ALTER TABLE [dbo].[CLDO_Queue] ADD CONSTRAINT [PK_CLDO_Queue_MemberID_QID_cl] PRIMARY KEY CLUSTERED (MemberID, QID) WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
      END
  END
  GO

  Is there a need for the following index:

  BEGIN
      IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[CLDO_Queue]') AND [name] = 'IX_CLDO_Queue_QID')
      BEGIN
          CREATE UNIQUE NONCLUSTERED INDEX [IX_CLDO_Queue_QID] ON
              [dbo].[CLDO_Queue](QID) WITH (ONLINE = ON, DATA_COMPRESSION = ROW);
      END
  END
  GO

  Thank You

  The second index ensures that QID is unique, the PK doesn't do this.

• GBeezy

  SSCarpal Tunnel

  Points: 4723

  More actions

  November 30, 2017 at 10:31 am

  #1970099

  Thank you both for the clarification!!