Do clustered indexes have to be unique?

What happens if a clustered index is not unique? Can it lead to bad performance because inserted rows flow to an "overflow" page of some sorts?

Is it "made" unique and if so how? What is the best way to make it unique?

I am asking because I am currently using a clustered index to divide my table in logical parts, but the performance is so-so, and recently I got the advice to make my clustered indexes unique. I'd like a second opinion on that.

Thanks!

Answers


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).


Need Your Help

Request timed out when very low traffic over Christmas Holiday week

asp.net sql-server-2005 iis-7 asp.net-ajax windows-server-2008

Over this past Christmas holiday week, when the website I work on was experiencing very low traffic, we saw several Request timed out exceptions (one on each day >> 12/26, 12/28, 12/29, and 12/30) on

Where in Mage Core is a product's status set?

magento status extend mage

I'm trying to extend the core to change how magento sets the status on products. Essentially, when an admin user tries to change the status of a product to disabled, I want it to check to see if the

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.