Clustered vs covering index
Consider the following table in SQL Server 2008:
LanguageCode varchar(10)
Language nvarchar(50)
LanguageCode participates in rel开发者_如何学Pythonationships, so I cannot create a primary key index that includes both columns (LanguageCode, Language).
If I put a primary clustered key on LanguageCode, of course I can't include Language in the index (covering index). This means that I will have to create a second index for Language, or run the risk of having duplicates in it (plus force a table scan to retrieve its value).
Further, MS's documentation (as well as experts on the subject) indicate that a table should ideally have a clustered index.
In this case, a non-clustered covering index (LanguageCode, Language) would not only ensure that Language is unique, but would avoid the table scan. However, there would be no "ideal" clustered index.
Is this one of those cases where having no clustered index is in fact ideal?
Edit based on feedback:
The only query I wish to run is:
SELECT Language, LanguageCode FROM Languages where Language="EN"
Clustered index, by definition, covers all columns.
If you create a PRIMARY KEY CLUSTERED
on LanguageCode
and a UNIQUE INDEX
on Language
, it will allow you to search a language both by its code and its name with a single lookup, and, in addition, make Language
unique.
There is no need to include columns on a clustered index. Since the clustered index is "the data", all columns are automatically included.
If you have a need to search by Language and/or ensure its uniqueness, then definitely create an additional index on it.
Based on the nature of the subject (which I'm guessing is languages spoken by humans), indexing for performance is going to be irrelevant. If you had 100 languages, and each row took 120 bytes (psuedo-factoring in varchar headers, null bitmasks, and whatnot), you'd have 12,000 bytes of data, which fits on two 8k pages. SQL will not bother to use indexes on anything that small, it'll just table scan the whole thing (2 pages) and brute force it, requiring less time than can readily be measured.
Indexing to ensure uniqueness, sure, I do it all the time. But for performance, until you hit 3 pages (or is it 4), it just doesn't matter. (Which will happen if you're tracking programming languages, 'cause there's a dozen new ones every week or so.)
精彩评论