SQL Server 索引 - HEAP 上的非聚集索引
我们的 SQL 数据库中有 221GB 的表，主要是重复数据。
团队在堆上创建了非聚集索引。这真的对性能有帮助吗？
我们是否应该将 IDENTITY 列放入表中，然后创建聚集索引，然后我们可以创建非聚集索引。
We have got 221gb table in our SQL Database, mainly duplicate data.
Team has created NON-CLUSTERED index on HEAP. Does really this help in terms of performannce?
Should we put IDENTITY column in table and then create CLUSTERED index AND after that we can create NON clustered indexes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这取决于
数据的使用模式和结构。
非聚集索引是否覆盖？
表中的数据是否经常变化？
具有覆盖的非聚集索引（或多个索引）的堆表可以优于聚集索引，其中聚集索引是唯一的“索引”（聚集索引显然总是覆盖，但可能不是最佳的查找
）聚集索引不是索引（在基于键查找数据存储位置的意义上），它是通过选择索引组织的整个表。在真正的（非聚集）索引中，索引中仅包含键和包含的列，这意味着（通常）每个数据库页可以存储更多行，并且不必要地读取更少的数据。
大多数表应该有聚集索引，但非聚集索引的选择才是大部分性能的来源。
It Depends
On the usage pattern and structure of the data.
Is the non-clustered index covering?
Is the data in the table ever changing?
A heap table with a non-clustered index (or indexes) which are covering can outperform a clustered index where the clustered index is the only "index" (a clustered index is obviously always covering, but may not be optimal for seeks)
Remember a clustered index is not an index (in the sense of a lookup based on a key into a location where the data is stored), it's the whole table organized by a choice of index. In a real (non-clustered) index, only the keys and included columns are included in the index and this means that (generally) more rows can be stored per database page and less data is read unnecessarily.
Most tables should have a clustered index, but the choice of non-clustered indexes is where most of your performance comes from.