Overlapping Indexes

Question

Overlapping Indexes

rerichards

SSChasing Mays

Points: 614
More actions
March 27, 2012 at 11:05 am

#391885

I have a Unique Constraint on a Clustered table named dbo.MyWidgetTable in
the form of :
CONSTRAINT UK_Widget_UID UNIQUE NONCLUSTERED (Widget_UID, WidgetColor)
This unique constraint is largely in place to assist with foreign key
relationships as the parent to child tables.
NOTE: There is an existing clustered index in place on the same table.
I also have two nonclustered indexes in the form of:
CREATE NONCLUSTERED INDEX IX_CreatedAt ON dbo.MyWidgetTable
(Widget_UID, WidgetColor, CreatedAt)
CREATE NONCLUSTERED INDEX IX_CreatedAt ON dbo.MyWidgetTable
(Widget_UID, WidgetColor, WidgetStatusFID)
As one can see, the nonclustered indexes overlap the unique constraint.
Is there a way around this overlapping index issue?
Because if I change the UNIQUE NONCLUSTERED index to have key columns
CreatedAt, WidgetStatusFID...and then try to create the following foreign key
constraint:
ALTER TABLE [dbo].[MyWidgetStatusTable] WITH NOCHECK ADD CONSTRAINT
[FK_MyWidgetStatusTable_MyWidgetTable] FOREIGN KEY([WidgetFID], [WidgetColor])
REFERENCES [dbo].[MyWidgetTable] ([Widget_UID], [WidgetColor])
I get the following error:
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.
MyWidgetTable' that match the referencing column list in the foreign key
'FK_MyWidgetStatusTable_MyWidgetTable'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
My question...I have overlapping indexes...How can I eliminate the
overlapping indexes while still keeping a unique constraint (and the existing
clustered index) in place that allows foreign key constraints to be created?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can't add two columns to the unique constraint without changing the uniqueness requirements of the table, and that's a bad thing.

If you really don't want duplicate indexes, reverse the key order of the nonclustered indexes

CREATE NONCLUSTERED INDEX IX_CreatedAt ON dbo.MyWidgetTable

(CreatedAt, Widget_UID, WidgetColor)

CREATE NONCLUSTERED INDEX IX_CreatedAt ON dbo.MyWidgetTable

(WidgetStatusFID, Widget_UID, WidgetColor)

(anything filtering on just Widget_UID, WidgetColor can use the unique constraint)

but if you do that, test and ensure that the queries that use those indexes aren't harmed in the process

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

One option to safely rid the database of one of the two overlapping indexes is to INCLUDE the third column in the unique index. Included columns are not considered in uniqueness checks, and any queries using the overalpping index can still be served from the UNIQUE INDEX without doing a lookup to the clustered index, provided the overlapping index was covering the query in the first place.

Example, redefining the UNIQUE INDEX this way...

CREATE UNIQUE NONCLUSTERED INDEX UK_Widget_UID ON dbo.MyWidgetTable

(Widget_UID, WidgetColor) INCLUDE (WidgetStatusFID);

makes this index redundant:

CREATE NONCLUSTERED INDEX IX_WidgetStatusFID ON dbo.MyWidgetTable

(Widget_UID, WidgetColor, WidgetStatusFID)

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato