3

There is much stuff around about 'unique' vs 'index' and I read lot of them, but still can't find explanation for my case.

In short, my database currently has a non-clustered index on two columns. We have some inconsistent state as a result of data duplication and I want to add a unique constraint on all four columns of the table. Since 'unique' will create an index under the hood does it still make sense to keep the old index around?

More specifically, it goes something like:

create table Stuff (
  id PK, 
  col1 varchar not null, 
  col2 varchar not null, 
  col3 bigint not null, 
  col4 bigint not null);
create index for_search on Stuff (col3, col4);
alter table Stuff 
  add constraint data_integrity_above_all 
  UNIQUE (col1, col2, col3, col4);

and if the answer is 'yes', then would it be a good move to specify UNIQUE (col3, col4, col1, col2); column order or is it too much implicit and not end well on maintenance?

More information about the nature of the data: col3 and col4 are another table ids (but not FK); col1, col2 -- user data.

DB: SQL Server 2012

  • 1
    Keeping around old indexes is really going to depend on whether or not they are used. Your query patterns play a huge part in that. At the very least you would be able to drop one of them (depending on the order of columns used in the constraint). Your data types could also play a role in this, and are worth incuding in your create table script. – Nic Nov 10 '16 at 22:54
  • 2
    Do you use id as a FK in any other table? If not, have you considered getting rid of id and defining col3, col4, col1, col2 as the Clustered PK and also getting rid of the for_search index? – Solomon Rutzky Nov 10 '16 at 23:08
  • @srutzky, true redundancy of id also came to my mind. But even if there is no FK reference today, there might be one in future. Of course real complication is in code base itself. It has 10+ years of development behind and I'm the new guy who asks too many questions) Eventually though I suggested to apply changes that align with @mendosi answer, It was declined. – Vehpsr Dfcbkmjdbx Nov 11 '16 at 19:41
2

If the purpose of the unique constraint (with its associated unique index) is simply to enforce uniqueness then you should change the column order for your unique constraint to col3, col4, col1, col2 and then your index will be redundant and can be dropped.

Otherwise there may be some benefit from having another index on your table with different leading columns, you should consider your workload, if the table is often accessed with a predicate on col1 or on both col1 and col2 and maybe that is a good case to have those columns first in the key.

Also @srutzky comment about the clustered index has merit and is worth considering.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.