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:
col4 are another table ids (but not FK);
col2 -- user data.
DB: SQL Server 2012