I've been asked to create an index on a database table. I've got very little info about the underlying database model, but that there shouldn't be any duplicates.
My questions are: In this regard does no duplicates mean the same as an Unique index? This index is to be for two columns, how can I easily check for duplicate values?
Any help will be greatly appreciated.
You would check for duplicates by doing a group by and checking for counts greater than 1:
select col1, col2, count(*)
from your_table
group by col1, col2
having count(*) > 1;
If you have a requirement that you cannot have more than one row with the same values in a set of columns (i.e. no duplicates), that's the same as saying those columns must be unique across all the rows.
If you want to enforce uniqueness, you would be best off creating a unique constraint (yes, a unique index will enforce the uniqueness, but creating a unique constraint on top of the index is better practice. The more information you can give to the optimizer, the greater the chance it will pick good execution paths for the queries!).
