SQL Server - 何时使用聚集索引与非聚集索引?

我知道聚集索引和非聚集索引之间的主要区别，并且了解它们的实际工作方式.我了解聚簇和非聚簇索引如何提高读取性能.但我不确定的一件事是，我会选择一个而不是另一个的原因是什么.

例如:如果一张表没有聚集索引，是否应该创建一个非聚集索引，这样做有什么好处

解决方案

我只想说一句警告:请非常小心选择您的聚集索引！每个常规"数据表都应该有一个聚集索引，因为拥有一个聚集索引确实会加速很多操作——是的，加速，甚至插入和删除！但前提是您选择了好的聚集索引.

它是 SQL Server 数据库中复制最多的数据结构.聚簇键也将成为表中每个非聚簇索引的一部分.

在选择聚类键时应该格外小心 - 它应该是:

• 窄(理想的 4 个字节)

• unique(毕竟它是行指针".如果你不让它独一无二，SQL Server 会在后台为你做，花费你几个字节每个条目乘以您拥有的行数和非聚集索引的数量 - 这可能非常昂贵！)

• 静态(永远不要改变 - 如果可能的话)

• 理想情况下不断增加，因此您不会以可怕的索引碎片告终(GUID 与良好的集群键完全相反 - 出于这个特殊原因)

• 它应该是不可为空的，理想情况下也是固定的宽度 - varchar(250) 是一个非常糟糕的聚类键

在这些点之后，其他任何事情都应该是第二和第三级的重要性......

查看 Kimberly Tripp(索引女王)关于该主题的一些博客文章 - 她在博客中写的任何内容都绝对无价 - 阅读它，消化它 - 以它为生！>

• GUID 作为主键和/或聚类键
• 聚集索引的争论仍在继续......
• 不断增加的聚类键 -聚集索引的争论..........再次！
• 磁盘空间很便宜 - 这不是点！

I know primary differences between clustered and non clustered indexes and have an understanding of how they actually work. I understand how clustered and non-clustered indexes improve read performance. But one thing I am not sure is that what would be the reasons where I would choose one over the other.

For example: If a table does not have a clustered index, should one create a non-clustered index and whats the benefit of doing

解决方案

I just want to put in a word of warning: please very carefully pick your clustered index! Every "regular" data table ought to have a clustered index, since having a clustered index does indeed speed up a lot of operations - yes, speed up, even inserts and deletes! But only if you pick a good clustered index.

It's the most replicated data structure in your SQL Server database. The clustering key will be part of each and every non-clustered index on your table, too.

You should use extreme care when picking a clustering key - it should be:

• narrow (4 bytes ideal)

• unique (it's the "row pointer" after all. If you don't make it unique SQL Server will do it for you in the background, costing you a couple of bytes for each entry times the number of rows and the number of nonclustered indices you have - this can be very costly!)

• static (never change - if possible)

• ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)

• it should be non-nullable and ideally also fixed width - a varchar(250) makes a very poor clustering key

Anything else should really be second and third level of importance behind these points ....

See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!

• GUIDs as PRIMARY KEYs and/or the clustering key
• The Clustered Index Debate Continues...
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!