Clustered index in VARCHAR column

Question

Can I create a clustered index in a VARCHAR column which is NOT UNIQUE and HAS NULL VALUES? But creating one really improves perfomance of an important query that is executed often. I just want to know if it is right to create a clustered index on a VARCHAR column.

Please advice.

Answer 1

Yes, you can create a clustered index on non-unique nullable varchar column. Is it good idea? It depends.

Ideal clustered index would be narrow, static and unique. CI key presents in every non-clustered index leaf (and for non-unique index also in intermediate and root levels). So wider CI is, wider non-clustered indexes are - which leads to less efficient NCI and overhead on maintenance. Whwn you modify the value of CI (static part), SQL Server physically moves the data in the CI as well as updates all NCI which could be quite expensive. Lastly, if you don't define CI as unique, SQL Server adds internal nullable integer called uniquifier to CI. Real overhead could be up to 8 bytes

As you can see, CI on varchar non-unique column violates criteria for "ideal CI". It does not mean though that it's the bad choice in your case - you just need to compare benefits you get with downsides it introduces. I personally would try to optimize the query with (covering) non-clustered indexes first.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Hi,

Another option is to use a covering index; these can be very useful for supporting frequently executed queries.  If you are using SQL 2005, or later, CREATE INDEX has the INCLUDE feature.  This would allow you to have your varchar column as the value stored in the index nodes, and the SELECT'ed columns in only the leaves.

If your important query is something like this:

select Col1, Col2, Col3, MyVarc
from dbo.MyTable
where MyVar = @SomeVarc;

your covering index would be:

CREATE INDEX IX_CoverMyVarc ON dbo.MyTable (MyVarc)
    INCLUDE (Col1, Col2, Col3);

This index is now basically a table that has the columns (Col1, Col2, Col3, MyVarc) and is clustered by MyVarc.  When your important query is run, it is fulfilled from only the index without going to the table's main clustered index.

By having only your varchar lookup column in the index nodes, you get two benefits:
1) Nodes are smaller and, thus, have faster lookup due to less nodes and more nodes per page.
2) It makes it easier to live within the 900 byte limit for maximum allowable size of the combined index values.

---

Dan Jameson
Manager SQL Server DBA
CureSearch for Children's Cancer
http://www.CureSearch.org

Answer 3

In addition , if it is often updated , it could lead you to heavy fragmentation and as  result performance degradation. Let it go and look at fragmentation to see how often it get fragmented??

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/