聚集索引与覆盖索引
请考虑 SQL Server 2008 中的下表：
LanguageCode varchar(10)
Language nvarchar(50)
LanguageCode 参与关系，因此我无法创建包含两列（LanguageCode、Language）的主键索引。
如果我在LanguageCode上放置一个主聚集键，我当然不能将Language包含在索引中（覆盖索引）。这意味着我必须为 Language 创建第二个索引，否则会面临其中存在重复项的风险（加上强制表扫描以检索其值）。
此外，MS 的文档（以及该主题的专家）表明表理想应该具有聚集索引。
在这种情况下，非聚集覆盖索引（LanguageCode，Language）不仅可以确保Language是唯一的，而且可以避免表扫描。然而，不会有“理想的”聚集索引。
这是没有聚集索引实际上是理想的情况之一吗？
根据反馈进行编辑：
我希望运行的唯一查询是：
SELECT Language, LanguageCode FROM Languages where Language="EN"
Consider the following table in SQL Server 2008:
LanguageCode varchar(10)
Language nvarchar(50)
LanguageCode participates in relationships, 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"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据定义，聚集索引涵盖所有列。
如果您在
LanguageCode上创建一个PRIMARY KEY CLUSTERED并在Language上创建一个UNIQUE INDEX，它将允许您搜索通过一次查找即可通过其代码和名称获得一种语言，此外，还可以使Language独一无二。Clustered index, by definition, covers all columns.
If you create a
PRIMARY KEY CLUSTEREDonLanguageCodeand aUNIQUE INDEXonLanguage, it will allow you to search a language both by its code and its name with a single lookup, and, in addition, makeLanguageunique.不需要在聚集索引上包含列。由于聚集索引是“数据”，因此会自动包含所有列。
如果您需要按语言搜索和/或确保其唯一性，那么一定要在其上创建一个附加索引。
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.
根据主题的性质（我猜是人类使用的语言），为性能建立索引将是无关紧要的。如果您有 100 种语言，并且每行占用 120 字节（varchar 标头、空位掩码等中的伪因子分解），那么您将拥有 12,000 字节的数据，适合两个 8k 页面。 SQL 不会费心在任何这么小的东西上使用索引，它只会表扫描整个东西（2 页）并暴力破解它，所需的时间比可以轻松测量的要少。
建立索引以确保唯一性，当然，我一直这样做。但就性能而言，直到你达到 3 页（或者是 4 页），这都无关紧要。 （如果您正在跟踪编程语言，就会发生这种情况，因为大约每周都会有十几种新语言。）
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.)