Difference Unique non unique indexes
This details is only for interview purpose to take a look before you jump for interview:
These two indexes are basically help to maintain data integrity by ensuring that no two rows of the data in the table has identical key values.
If you create unique index on existing table with data then the values in columns or expressions which comprise index key are checked for the uniqueness. If table contains rows with duplicate key then the index creation process will fail. When unique index is defined on the table then uniqueness will enforced whenever keys are added or changed within index. This enforcement is includes update, load, import, insert and set integrity etc. In addition uniqueness of data values, the unique index could also used to improve data retrieval operation performance during query processing.
On other side Non unique indexes are not used to enforce constraints on tables wherever they are associated. Although, non-unique indexes are used only to improve query performance by maintaining sorted order of data values that used frequently.
Excluding and Including NULL keys:
Non-Unique and unique indexes can be created so that key is not inserted into the index object, if all the columns or expressions of key are null. Although excluding the null keys could be result in improved storage and performance optimization in many cases where you don’t queries to access data associated with null keys. For unique indexes, enforcement of uniqueness of table data ignores rows where the index key is null.
Differences between unique key or primary key constraints and unique indexes:
There is no significant difference between the unique key or primary key constraint and unique index. To implement concept of the primary and unique key constraints, database manager uses combination of a unique index and the NOT NULL constraint. So that, unique indexes don’t enforce primary key constraints by themselves because they allow null values. Null values represent unknown values when it comes to indexing, null value is treated equal to other null values. So that if unique index consists of single column then only one null value is allowed, more than one null value would violate unique constraint. Also, if unique index consists of the multiple columns then specific combination of values and nulls could be used only one at time.
Reference:
