请问一下SQL SERVER 索引的小问题

Question

本人对索引的理解。

聚集索引：对基础表的指定列进行物理的排序。

非聚集索引：对指定列进行排序。(排序出来的数据不存储在基础表中)

不知道理解是否正确。如果是正确的请问非聚集索引的排序出来的数据存储的位置。谢谢

Answer 1

怎么没有人啊？？？

Answer 2

The difference is that Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Hope the following 2 charts could help you understand:

Clustered index

non-clustered index

---

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.

Answer 3

聚集索引是逻辑上对数据页进行了排序 物理磁盘没有排序

非聚集索引是在索引页上 按照键值进行排序 它的排序体现在索引页 但是数据页 还是一样凌乱的（除非表上还有聚集索引）

Answer 4

那索引页存储在哪里呢？

Answer 5

Stored in B-tree format in the table.

Answer 6

B树的表是在那一个数据库中呢？谢谢！

Answer 7

那索引页存储在哪里呢？

数据库文件里

Answer 8

B树的表是在那一个数据库中呢？谢谢！

There's no b-tree table.

Answer 9

索引(聚簇的,非聚簇的)都是保存在该数据库的数据文件中的. SQL Server 的存储最小单元是页(page,8KB), 还有就是Extent.

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

---

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.

Answer 10

3Q......