I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our competing resources.
I have previously read a discussion that stated a
Unique index is no additional cost to maintain, since an
Insert operation implicitly checks for where it fits into the B-tree, and, if a duplicate is found in a non-unique index, appends a uniquifier to the end of the key, but otherwise inserts directly. In this sequence of events, a
Unique index has no additional cost.
My coworker combats this statement by saying that
Unique is enforced as a second operation after the seek to the new position in the B-tree, and thus is more costly to maintain than a non-unique index.
At worst, I have seen tables with an identity column (inherently unique) that is the clustering key of the table, but explicitly stated as non-unique. On the other side of worst is my obsession with uniqueness, and all indexes are created as unique, and when not possible to define an explicitly unique relation to an index, I append the PK of the table to the end of the index to ensure the uniqueness is guaranteed.
I'm frequently involved in code reviews for the dev team, and I need to be able to give general guidelines for them to follow. Yes, every index should be evaluated, but when you have five servers with thousands of tables each and as many as twenty indexes on a table, you need to be able to apply some simple rules to ensure a certain level of quality.
Does uniqueness have an additional cost on the back-end of an
Insert compared to the cost of maintaining a non-unique index? Secondly, what is wrong with appending the Primary Key of a table to the end of an index to ensure uniqueness?
Example Table Definition
create table #test_index ( id int not null identity(1, 1), dt datetime not null default(current_timestamp), val varchar(100) not null, is_deleted bit not null default(0), primary key nonclustered(id desc), unique clustered(dt desc, id desc) ); create index [nonunique_nonclustered_example] on #test_index (is_deleted) include (val); create unique index [unique_nonclustered_example] on #test_index (is_deleted, dt desc, id desc) include (val);
An example of why I would add the
Unique key to the end of an index is in one of our fact tables. There is a
Primary Key that is an
Identity column. However, the
Clustered Index is instead the partitioning scheme column, followed by three foreign key dimensions with no uniqueness. Select performance on this table is abysmal, and I frequently get better seek times using the
Primary Key with a key lookup rather than leveraging the
Clustered Index. Other tables that follow a similar design, but have the
Primary Key appended to the end have considerably better performance.
-- date_int is equivalent to convert(int, convert(varchar, current_timestamp, 112)) if not exists(select * from sys.partition_functions where [name] = N'pf_date_int') create partition function pf_date_int (int) as range right for values (19000101, 20180101, 20180401, 20180701, 20181001, 20190101, 20190401, 20190701); go if not exists(select * from sys.partition_schemes where [name] = N'ps_date_int') create partition scheme ps_date_int as partition pf_date_int all to ([PRIMARY]); go if not exists(select * from sys.objects where [object_id] = OBJECT_ID(N'dbo.bad_fact_table')) create table dbo.bad_fact_table ( id int not null, -- Identity implemented elsewhere, and CDC populates date_int int not null, dt date not null, group_id int not null, group_entity_id int not null, -- member of group fk_id int not null, -- tons of other columns primary key nonclustered(id, date_int), index [ci_bad_fact_table] clustered (date_int, group_id, group_entity_id, fk_id) ) on ps_date_int(date_int); go if not exists(select * from sys.objects where [object_id] = OBJECT_ID(N'dbo.better_fact_table')) create table dbo.better_fact_table ( id int not null, -- Identity implemented elsewhere, and CDC populates date_int int not null, dt date not null, group_id int not null, group_entity_id int not null, -- member of group -- tons of other columns primary key nonclustered(id, date_int), index [ci_better_fact_table] clustered(date_int, group_id, group_entity_id, id) ) on ps_date_int(date_int); go