What is difference between Primary Key and Unique key?
Q: What is difference between Primary Key and Unique key, What are the different types of key used in sql server and what is its use what is rollup and cude functions?
Bryan Oliver says: A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.
When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.
* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.
Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.