We have got 221gb table in our SQL Database, mainly duplicate data.
我们的SQL数据库中有221gb的表，主要是重复数据。
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.
我们应该将IDENTITY列放在表中，然后创建聚集索引，然后我们可以创建非聚集索引。
1 个解决方案
#1
4
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.
大多数表都应该有一个聚集索引，但是选择非聚集索引是性能的主要来源。
#1
4
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.
大多数表都应该有一个聚集索引，但是选择非聚集索引是性能的主要来源。