一个非常简单的、非技术性的经验法则是，聚集索引通常用于主键(或者至少是唯一的列)，而非聚集索引用于其他情况(可能是外键)。实际上，SQL Server默认会在你的主键列上创建一个聚集索引。正如您将了解到的，聚类索引与数据在磁盘上物理排序的方式有关，这意味着对于大多数情况，它是一个很好的全面选择。

一个非常简单的、非技术性的经验法则是，聚集索引通常用于主键(或者至少是唯一的列)，而非聚集索引用于其他情况(可能是外键)。实际上，SQL Server默认会在你的主键列上创建一个聚集索引。正如您将了解到的，聚类索引与数据在磁盘上物理排序的方式有关，这意味着对于大多数情况，它是一个很好的全面选择。

聚集索引: 如果表上不存在聚集索引，主键约束将自动创建聚集索引。聚类索引的实际数据可以存储在索引的叶级。

Non Clustered Index: Actual data of non clustered index is not directly found at leaf node, instead it has to take an additional step to find because it has only values of row locators pointing towards actual data. Non clustered Index can't be sorted as clustered index. There can be multiple non clustered indexes per table, actually it depends on the sql server version we are using. Basically Sql server 2005 allows 249 Non Clustered Indexes and for above versions like 2008, 2016 it allows 999 Non Clustered Indexes per table.

下面是聚类索引和非聚类索引的一些特征:

聚集索引

聚集索引是唯一标识SQL表中的行的索引。 每个表只能有一个聚集索引。 可以创建包含多个列的聚集索引。例如:create Index index_name(col1, col2, col.....)。 默认情况下，具有主键的列已经具有聚集索引。

非聚簇索引

非聚集索引类似于简单索引。它们只是用于快速检索数据。不一定有唯一的数据。

我知道这是一个非常古老的问题，但我想我可以提供一个类比来帮助说明上面的答案。

聚集索引

If you walk into a public library, you will find that the books are all arranged in a particular order (most likely the Dewey Decimal System, or DDS). This corresponds to the "clustered index" of the books. If the DDS# for the book you want was 005.7565 F736s, you would start by locating the row of bookshelves that is labeled 001-099 or something like that. (This endcap sign at the end of the stack corresponds to an "intermediate node" in the index.) Eventually you would drill down to the specific shelf labelled 005.7450 - 005.7600, then you would scan until you found the book with the specified DDS#, and at that point you have found your book.

非聚簇索引

But if you didn't come into the library with the DDS# of your book memorized, then you would need a second index to assist you. In the olden days you would find at the front of the library a wonderful bureau of drawers known as the "Card Catalog". In it were thousands of 3x5 cards -- one for each book, sorted in alphabetical order (by title, perhaps). This corresponds to the "non-clustered index". These card catalogs were organized in a hierarchical structure, so that each drawer would be labeled with the range of cards it contained (Ka - Kl, for example; i.e., the "intermediate node"). Once again, you would drill in until you found your book, but in this case, once you have found it (i.e, the "leaf node"), you don't have the book itself, but just a card with an index number (the DDS#) with which you could find the actual book in the clustered index.

当然，没有什么能阻止图书管理员复印所有的卡片，并将它们按不同的顺序分类在一个单独的卡片目录中。(通常至少有两个这样的目录:一个按作者姓名排序，另一个按标题排序。)原则上，您可以拥有任意数量的这些“非聚集”索引。

聚集索引

聚类索引决定了表中DATA的物理顺序。因此，一个表只有一个聚集索引(主键/组合键)。

“字典”不需要任何其他索引，它已经根据单词索引

非聚集索引

非聚集索引类似于Book中的索引。数据存储在一个地方。索引存储在另一个位置，并且索引具有指向存储位置的指针。这有助于快速搜索数据。因此，一个表有超过1个非聚集索引。

“生物书”在开头有一个单独的索引指向章节的位置，在“结尾”有另一个索引指向常用单词的位置