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

使用聚集索引，行按与索引相同的顺序物理存储在磁盘上。因此，只能有一个聚集索引。

对于非聚集索引，有第二个列表，其中包含指向物理行的指针。您可以有许多非聚集索引，尽管每个新索引都会增加写入新记录的时间。

如果想要返回所有列，从聚集索引中读取通常更快。您不必先访问索引，再访问表。

如果需要重新排列数据，则写入具有聚集索引的表可能会较慢。

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

聚集索引

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.

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

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

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 Server默认会在你的主键列上创建一个聚集索引。正如您将了解到的，聚类索引与数据在磁盘上物理排序的方式有关，这意味着对于大多数情况，它是一个很好的全面选择。

聚集索引

聚集索引根据表或视图中的键值对数据行进行排序和存储。这些是包含在索引定义中的列。每个表只能有一个聚集索引，因为数据行本身只能按一种顺序排序。

只有当表中包含聚集索引时，表中的数据行才会按排序顺序存储。当一个表具有聚集索引时，这个表称为聚集表。如果表没有聚集索引，则其数据行存储在称为堆的无序结构中。

非聚集

Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

可以将非键列添加到非聚集索引的叶级，以绕过现有的索引键限制，并执行完全覆盖的索引查询。有关更多信息，请参见创建包含列的索引。有关索引键限制的详细信息，请参见SQL Server最大容量规格。

参考:https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described