如果我使用群集和非群集索引，我完全会感到困惑。 [英] I am totally get confuse when should i use cluster and non cluster index.

问题描述

什么时候使用集群索引和非集群索引。这个索引在性能方面会更优先。你能帮我一个例子吗？

解决方案

聚集索引超快。这是因为，它不是索引列的JUST索引，而是逐行的所有列的索引。这意味着SQL Server查找索引并查找整行。这就是为什么你只能有一个聚簇索引。这非常快。这也意味着插入，特别是删除，真的很慢。对您经常阅读的数据使用聚簇索引，很少写入。否则，使用普通索引。如果添加到列表但从不从中删除，则它也是聚簇索引的候选者。问题是，如果删除，下面的所有行都需要复制，请记住聚集索引具有索引级别的所有数据，因此需要填充删除留下的漏洞。



如果您还想要删除数据的聚簇索引的速度，请考虑使用已删除的标志来过滤掉已删除的行（这也需要一个索引，但不需要群集索引） ）。

表上只能有一个聚簇索引，因为它定义了数据实际存储在磁盘上的方式。



请参阅 http://technet.microsoft.com/en-us/library/ms190457.aspx [ ^ ]了解更多信息。

when to use cluster index and non- cluster index.which index would be more preffered in terms of performance.Can you please help me with an example?

解决方案

A clustered index is SUPER fast. This is because, instead of an index of JUST the index column, it's an index of ALL your columns, row by row. That means that SQL Server looks up the index and finds the whole row. This is why you can only have one clustered index. This is incredibly fast. It also means that inserts and especially deletes, are REALLY slow. Use a clustered index for data you read to a lot, and rarely write to. Otherwise, use a normal index. If you add to a list but never delete from it, it's also a candidate for a clustered index. The issue is, if you delete, all the rows below need to be copied up, remember that a clustered index has ALL The data at the index level, so the hole left by a delete needs to be filled.

If you need the speed of a clustered index on data you want to also delete, consider using a deleted flag to filter out deleted rows ( this would need an index too, but not the clustered one ).

There can only be one clustered index on a table because it defines how the data is actually stored on the disk.

See http://technet.microsoft.com/en-us/library/ms190457.aspx[^] for more info.

这篇关于如果我使用群集和非群集索引，我完全会感到困惑。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！