主键Ascending与Descending [英] Primary key Ascending vs Descending

问题描述

在Sql Server中，我有一个带有Identity主键的表。通常我想要最新的几条新记录，所以我通过降低主键来获取排序的前n个。我应该将主键索引定义为降序，还是没有区别？即如果它们按升序排列，那么sql可以有效地向后工作吗？

In Sql Server, I have a table with an Identity primary key. Often I want the latest few new records, so I grab the Top n sorted by descending the primary key. Should I define the Primary Key index as Descending, or does it make no difference? i.e. if they are in Ascending order, then can sql just as efficiently work backwards?

推荐答案

从纯粹的查询角度来看，它使得如果你想拉N个最近或N个最旧的记录，你的密钥是下降还是上升没有区别：

From a purely querying standpoint, it makes no difference whether your key is descending or ascending if you want to pull N most recent or N oldest records:

SQL Server的内部算法无论存储密钥的顺序如何，都可以在单列索引上双向导航。例如，在单列索引上指定DESC不会使使用ORDER BY IndexKeyCol DESC子句的查询运行得比为索引指定ASC更快。

The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. For example, specifying DESC on a single-column index does not make queries with an ORDER BY IndexKeyCol DESC clause run faster than if ASC was specified for the index.

http://msdn.microsoft。 com / en-us / library / aa933132（SQL.80）.aspx

然而几乎在任何正常情况下， 您希望主键升序并且通常是顺序排列以防止碎片。 SQL Server经过优化，可以将新记录物理附加到数据库文件的末尾。如果它需要在顶部插入每个新记录并将所有内容推下来，则可能会导致几乎100％的碎片。

However under just about any normal circumstance, you want your primary key to be ascending and ordinally sequential to prevent fragmentation. SQL Server is optimized for physically appending new records to the end of the database file. If it needs to insert each new record at the top and push everything down, it would probably result in nearly 100% fragmentation.

这篇关于主键Ascending与Descending的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！