优先使用单个表的Clustered或nonclustered索引 [英] Preffered which Clustered or nonclustered index for single table

问题描述

如果我有一个表，我必须使用一个来自聚簇或非聚集索引的表，我应该优先考虑以获得更好的性能：
 
我正在考虑聚集索引非聚集索引使用额外的键查找&额外存储空间
 
请提及您对上述情况的评论。 
 
感谢所有人..... 

新年快乐2014代码项目成员

解决方案

HI

首先，您需要了解聚簇索引和非聚簇索引之间的差异。

聚簇索引必须是应用于表的唯一键和非聚集索引必须应用于该表的任何键。并且您的表中只有一个聚簇索引，并且在创建该表的主键时将默认创建该索引。



因此，如果要根据索引改进选择查询，则应使用在该查询的where子句中使用的列创建非聚集索引。给出填充因子大约70-80％。



并使用执行计划查询，以便建议你索引。



更多信息：



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



http://technet.microsoft.com/en-us/library/ms189280.aspx [ ^ ]

添加更多细节：



聚簇索引将ENTIRE行存储为指数。这意味着它超快，因为所有数据都存在于索引所在的节点上。这意味着您的整个表按顺序存储在数据库中。这意味着，如果删除行，则需要向下复制该行上方的所有行以维护索引。因此，聚簇索引对于读取来说速度非常快，编辑速度可能会很慢（取决于编辑是否会使行占用的空间大于启动时可用于行的空间）以及删除速度非常慢。对于永不改变的归档数据来说，它非常棒。它非常适合你经常阅读并需要超快的东西，但很少有变化。对于经常变化的数据来说太可怕了。



和SQL一样，答案是，它取决于，并且有两个选项存在的原因，每个选项有时候是最好的选择。



您提到存储空间是一个问题。除非您在Google工作，否则您的数据库大小似乎不太可能导致大量硬件成本。确保你优化重要的事情，而不是盲目地做任何你能想到的事情。

是的，你知道集群和非集群索引之间的区别。

你也必须在你的数据库中清楚你想要的东西。



见下面的例子： -

非比较具有聚集索引的聚簇索引和示例



作为非聚集索引的示例，假设我们在EmployeeID列上有非聚集索引。非聚集索引将存储EmployeeID的值和指向Employee表中实际存储该值的行的指针。但另一方面，聚簇索引实际上将存储特定EmployeeID的行数据 - 因此，如果您正在运行查找EmployeeID为15的查询，则表中其他列的数据（如EmployeeName，EmployeeAddress等）将所有实际存储在聚集索引本身的叶节点中。



这意味着对于非聚集索引，需要额外的工作来跟踪指针表中的行用于检索任何其他所需的值，而不是直接访问行的聚簇索引，因为它的存储顺序与聚簇索引本身的顺序相同。因此，从聚簇索引读取通常比从非聚集索引读取更快。





更多细节请参考以下网站： -

http：/ /stackoverflow.com/questions/18304376/sql-server-when-to-use-clustered-vs-non-clustered-index [ ^ ]

SQL 2005中的集群和非集群索引 [ ^ ]

If i have one table ,i have to use one from clustered or non clustered index to which i should prefered for better performance :

I am thinking about Clustered index since non clustered index uses extra key look up & extra storage space 

Please mentioned ur review about above scenario.

Thanks to all.....

Happy new year 2014 to code projects members

解决方案

HI
first of all you need to understand diff between clustered and non clustered index.
Clustered index must be applied on unique key of table and non-clustered index must be applied on any key of that table. and you have only one clustered index in your table and it will be created by default when you create Primary key of that table.

So if you want to improve your select query based on index , you should create non-clustered index with the columns which you used in where clause of that query.and give fill factor about 70-80%.

And use execution plan for your query so it will suggest you index.

FOr more information :

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

http://technet.microsoft.com/en-us/library/ms189280.aspx[^]

To add some more detail:

A clustered index stores the ENTIRE row as the index. This means that it's super fast because ALL the data exists on the node where the index lies. This means that your whole table is stored sequentially in the database. What this means is, if you delete a row, all the rows above that row, need to be copied down to maintain the index. So, a clustered index is super fast for reads, can be slow for edits ( depending on if your edit makes the row take up more room than is available for the row when you start ) and very slow for deletes. It's great for archived data that will never change. It's great for stuff you'll read often and need to be super fast, but rarely changes. It's horrible for data that gets changed constantly.

As always with SQL, the answer is, it depends, and there's a reason both options exist, each is sometimes the best choice.

You mention storage space as a concern. Unless you work for Google, it seems unlikely that your DB size is going to run in to significant hardware costs. Make sure you optimise on what matters, not blindly on anything you can think of.

yes, you know the difference between cluster and non-cluster index.
also you have to clear your senario what you want exactly in your db.

see below example:-
A comparison of a non-clustered index with a clustered index with an example

As an example of a non-clustered index, let’s say that we have a non-clustered index on the EmployeeID column. A non-clustered index will store both the value of the EmployeeID AND a pointer to the row in the Employee table where that value is actually stored. But a clustered index, on the other hand, will actually store the row data for a particular EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data from other columns in the table like EmployeeName, EmployeeAddress, etc. will all actually be stored in the leaf node of the clustered index itself.

This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generally faster than reading from a non-clustered index.


for more detail refer below sites:-
http://stackoverflow.com/questions/18304376/sql-server-when-to-use-clustered-vs-non-clustered-index[^]
Clustered and Non-Clustered Index in SQL 2005[^]

这篇关于优先使用单个表的Clustered或nonclustered索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！