Sql-server – ny tangible difference between a unique clustered index and a clustered primary key

Primary index

A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates. Also Called a Clustered index. eg. Employee ID can be Example of it.

Secondary index

A Secondary index is an index that is not a primary index and may have duplicates. eg. Employee name can be example of it. Because Employee name can have similar values.

Dense Index

Index record appears for every search­ key value in the file. Dense indexes point directly to individual records.

Sparse index

contains index records for only some search ­key values. Applicable when records are sequentially ordered on search ­key. Just as with book indexes, sparse database indexes don’t point to individual records, but to ‘pages'

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keyword on it you are actually creating a separate index. This does not only make things less efficient but, in some cases, it will make your queries less performant.

You can check that two index were created by doing:

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Both in MyISAM and InnoDB, it will require additional space, it will make the writes slower (because the index has to be updated synchronously at write time), and it may waste optimizer (even if microseconds) time deciding which index is the best one. MySQL would usually select the PRIMARY key in MyISAM, but if for any reason both indexes were used, you will also waste memory.

If you do not believe me, believe MySQL:

mysql> INSERT test1 SELECT * from test;
Query OK, 1691648 rows affected (6.84 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE test2 add unique(id);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> INSERT test2 SELECT * from test;
Query OK, 1691648 rows affected (10.94 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

A run of pt-duplicate-key-checker is usually enough to detect those duplicates.

If you mean using UNIQUE NOT NULL instead of PRIMARY KEY, I would not expect a difference on MyISAM in most cases (although some tools work better with a PK, like row-based replication); it is not the case with InnoDB, in which the PK is fundamental on its structure (clustered rows around it).