A four-byte internal column is automatically created by SQL Server when clustered index created on a non-unique column. This column is referred to as UNIQUIFIER.
A clustered index can be created on the non-unique and nullable column but SQL Server must need a unique identifier to sort the page and to locate the rows. So, it internally assigns a unique number to each duplicate row.
This UNIQUIFIER is not visible as it is created for an internal purpose but it can be seen when examining the data pages.
The following example shows how the hidden field can be viewed when it is created automatically by SQL Server.
CREATE TABLE dbo.emp ( empno INT, empname VARCHAR(10), empdoj DATE ) INSERT INTO dbo.emp (empno, empname, empdoj) VALUES (1, 'Joel', '20110622'), (1, 'Lucas', '20130322') CREATE CLUSTERED INDEX ix_emp_empno ON dbo.emp(empno) DBCC IND (11, 'emp', -1); DBCC PAGE (11, 1, 600, 3);
The following image is the output of the DBCC Page command. It clearly shows that 4 bytes filed called UNIQUIFIER is populated for each row.
