I was surprised to know that SQL Server (I tested the 2008 R2 edition, but this seems to work in earlier versions too) allows inserting data into tables which do not have a primary key (and hence a clustered index) defined.
我很惊讶地知道SQL Server(我测试了2008 R2版本,但这似乎也适用于早期版本)允许将数据插入到没有定义主键(因此聚集索引)的表中。
How is it possible and how the data are stored physically for these tables?
如何以及如何为这些表物理存储数据?
1 个解决方案
#1
3
Helpful link here:
有用的链接:
Q: I’ve heard that the clustered index is “the data,” but I don’t fully understand what that means. If I drop a clustered index, will I lose the data?
问:我听说聚集索引是“数据”,但我不完全理解这意味着什么。如果我删除聚集索引,我会丢失数据吗?
A: I get asked this question a lot, and I find that index structures tend to confuse people; indexes seem mysterious and, as a result, are unintentionally thought of as very complicated. A table can be stored internally with or without a clustered index. If a table doesn’t have a clustered index, it’s called a heap. If the table has a clustered index, it’s often referred to as a clustered table. When a clustered index is created, SQL Server will temporarily duplicate and sort the data from the heap into the clustered index key order (because the key defines the ordering of the data) and remove the original pages associated with the heap. From this point forward, SQL Server will maintain order logically through a doubly-linked list and a B+ tree that’s used to navigate to specific points within the data.
答:我经常被问到这个问题,我发现索引结构往往会让人感到困惑;索引看起来很神秘,因此无意中被认为是非常复杂的。表可以在内部存储,有或没有聚簇索引。如果表没有聚簇索引,则称为堆。如果表具有聚簇索引,则通常将其称为聚簇表。创建聚簇索引时,SQL Server将临时复制堆中的数据并将其排序为聚簇索引键顺序(因为键定义了数据的顺序)并删除与堆关联的原始页。从现在开始,SQL Server将通过双向链表和用于导航到数据中特定点的B +树逻辑维护顺序。
#1
3
Helpful link here:
有用的链接:
Q: I’ve heard that the clustered index is “the data,” but I don’t fully understand what that means. If I drop a clustered index, will I lose the data?
问:我听说聚集索引是“数据”,但我不完全理解这意味着什么。如果我删除聚集索引,我会丢失数据吗?
A: I get asked this question a lot, and I find that index structures tend to confuse people; indexes seem mysterious and, as a result, are unintentionally thought of as very complicated. A table can be stored internally with or without a clustered index. If a table doesn’t have a clustered index, it’s called a heap. If the table has a clustered index, it’s often referred to as a clustered table. When a clustered index is created, SQL Server will temporarily duplicate and sort the data from the heap into the clustered index key order (because the key defines the ordering of the data) and remove the original pages associated with the heap. From this point forward, SQL Server will maintain order logically through a doubly-linked list and a B+ tree that’s used to navigate to specific points within the data.
答:我经常被问到这个问题,我发现索引结构往往会让人感到困惑;索引看起来很神秘,因此无意中被认为是非常复杂的。表可以在内部存储,有或没有聚簇索引。如果表没有聚簇索引,则称为堆。如果表具有聚簇索引,则通常将其称为聚簇表。创建聚簇索引时,SQL Server将临时复制堆中的数据并将其排序为聚簇索引键顺序(因为键定义了数据的顺序)并删除与堆关联的原始页。从现在开始,SQL Server将通过双向链表和用于导航到数据中特定点的B +树逻辑维护顺序。