Do clustered indexes have to be unique?

by

They don’t have to be unique but it certainly is encouraged.
I haven’t encountered a scenario yet where I wanted to create a CI on a non-unique column.

What happens if you create a CI on a non-unique column

If the clustered index is not a unique
index, SQL Server makes any duplicate
keys unique by adding an internally
generated value called a uniqueifier

Does this lead to bad performance?

Adding a uniqueifier certainly adds some overhead in calculating and in storing it.
If this overhead will be noticable depends on several factors.

  • How much data the table contains.
  • What is the rate of inserts.
  • How often is the CI used in a select (when no covering indexes exist, pretty much always).

Edit
as been pointed out by Remus in comments, there do exist use cases where creating a non-unique CI would be a reasonable choice. Me not having encountered one off those scenarios merely shows my own lack of exposure or competence (pick your choice).

More Related Contents:

  1. Procedure or function !!! has too many arguments specified
  2. SQL Server equivalent of WM_CONCAT function [duplicate]
  3. to select all the employees who have joined most recently under king
  4. Unpivot with column name
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. How to group time by hour or by 10 minutes
  8. Convert varchar into datetime in SQL Server
  9. T-SQL Cast versus Convert
  10. Check if a row exists, otherwise insert
  11. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  12. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  13. Convert Xml to Table SQL Server
  14. Need to list all triggers in SQL Server database with table name and table’s schema
  15. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  16. How does this CASE expression reach the ELSE clause?
  17. TransactSQL to run another TransactSQL script
  18. Conditional logic in PostDeployment.sql script using SQLCMD
  19. T-SQL and the WHERE LIKE %Parameter% clause
  20. Store multiple bit values in a single table column
  21. How can I group time by hour or by 10 minutes?
  22. Know relationships between all the tables of database in SQL Server
  23. SQL Server equivalent to MySQL enum data type?
  24. Comma separated values in a database field
  25. Grant SELECT permission on a view, but not on underlying objects
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. Average of multiple columns
  28. COALESCE Function in TSQL
  29. Execute sp_executeSql for select…into #table but Can’t Select out Temp Table Data
  30. Select rows where column value has changed

Leave a Comment