sql server中的聚簇索引和非聚簇索引之间的区别 [英] Difference between clustered and non clustered index in sql server

问题描述

sql server中的聚簇索引和非聚簇索引有什么区别。我们在sql server中设计表时会使用什么。

解决方案

虽然我同意回答这个问题的最佳方法是谷歌，但我只想添加一个群集index对于搜索来说非常快，并且减慢了删除和（在较小程度上）数据更改的速度。如果您的表上有主键，则默认情况下它是聚簇索引，因此如果您想要另一个聚簇索引，则需要将主键标记为NOT clustered。

A clusched index是一种特殊类型的索引，它重新排序表中记录的物理存储方式。

因此，表只能有一个聚簇索引，这通常在主键上进行。聚簇索引的叶节点包含数据页。



非聚簇索引是一种特殊类型的索引，其中索引的逻辑顺序与物理不匹配存储在磁盘上的行的顺序。非聚簇索引的叶节点不包含数据页。相反，叶子节点包含索引行。

每个表可以有多达249个非聚集索引

请通过

1. SQL 2005中的群集和非群集索引 [ ^ ]。

2. SQL Server 2005/2008表索引概述（第1部分） [ ^ ]

3. 数据库性能优化第1部分（索引策略） [ ^ ]

4. 加速数据库访问 - 第3部分：修复缺失的索引 [ ^

what is the difference between clustered and non clustered index in sql server. what will we use while designing table in sql server.

解决方案

While I agree that the best way to answer this would have been google, I will just add that a clustered index is super fast for searches, and slows down deletes and ( to a lesser degree ) changes to data. If you have a primary key on your table, it is a clustered index by default, so if you want to have another clustered index, your primary key needs to be marked NOT clustered.

A clustered index is a special type of index that reorders the way records in the table are physically stored.
Therefore table can have only one clustered index and this is usually made on the primary key. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be as many as 249 nonclustered index per table

Please go through
1. Clustered and Non-Clustered Index in SQL 2005[^].
2. Overview of SQL Server 2005/2008 Table Indexing (Part-1)[^]
3. Database performance optimization part 1 (Indexing strategies)[^]
4. Speeding up database access - Part 3: Fixing missing indexes[^]

这篇关于sql server中的聚簇索引和非聚簇索引之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！