Geek Answers Handbook

Cluster vs. Coverage Index

Consider the following table in SQL Server 2008:

LanguageCode varchar(10) Language nvarchar(50)

LanguageCode is involved in the relationship, so I cannot create a primary key index that includes both columns (LanguageCode, Language).

If I put the main cluster key in LanguageCode, of course, I cannot include Language in the index (coverage index). This means that I will need to create a second index for the language or the risk of duplicates in it (plus force the table scan to get its value).

In addition, MS documentation (as well as experts on this) indicate that the table ideally has a clustered index.

In this case, the nonclustered coverage index (LanguageCode, Language) not only guarantees the uniqueness of the language, but also avoids scanning the table. However, there would be no “ideal” clustered index.

Is this one of those cases where the absence of a clustered index is actually ideal?

Change based on feedback:

The only request I want to run is:

 SELECT Language, LanguageCode FROM Languages where Language="EN"
+4
source share
3 answers

A clustered index, by definition, covers all columns.

If you create PRIMARY KEY CLUSTERED on LanguageCode and UNIQUE INDEX on Language , this will allow you to search for a language both by its code and by name with one search and, in addition, make Language unique.

+6
source

  • No need to include columns in a clustered index. Because a clustered index is “data,” all columns are automatically included.

  • If you need to search by language and / or ensure its uniqueness, be sure to create an additional index on it.

+4
source

Based on the nature of the subject (which, I assume, is the language people speak), performance indexing will not be appropriate. If you had 100 languages ​​and each line occupied 120 bytes (psuedo factoring in varchar headers, zero bitmaps and something else), you would have 12,000 bytes of data that would correspond to two 8k pages. SQL will not try to use indexes on anything small, it just scans all this (2 pages) and brute force, requiring less time than can be easily measured.

Indexing to ensure uniqueness, of course, I do this all the time. But for performance, until you click 3 pages (or it's 4), it just doesn't matter. (What happens if you keep track of programming languages ​​because there will be a dozen new ones a week or so.)

0
source

More articles:


All Articles