Duplicate Index

Question

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

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

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

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 · Answer 2

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 · Answer 3

GBeezy

SSCarpal Tunnel

Points: 4723

November 30, 2017 at 10:31 am

#1970099

Thank you both for the clarification!!