没有默认索引的主键（排序）- SQL2005
如何关闭主键上的默认索引 我不希望所有表都被索引（排序），但它们必须有主键
How do I switch off the default index on primary keys
I dont want all my tables to be indexed (sorted) but they must have a primary key
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以将主键索引定义为
NONCLUSTERED，以防止表行根据主键进行排序，但您无法在没有某些关联索引的情况下定义主键。You can define a primary key index as
NONCLUSTEREDto prevent the table rows from being ordered according to the primary key, but you cannot define a primary key without some associated index.表始终是未排序的 - 表没有“默认”顺序，优化器可能会或可能不会选择使用索引（如果存在）。
在 SQL Server 中，索引实际上是实现键的唯一方法。您可以在聚集索引或非聚集索引之间进行选择——仅此而已。
Tables are always unsorted - there is no "default" order for a table and the optimiser may or may not choose to use an index if one exists.
In SQL Server an index is effectively the only way to implement a key. You get a choice between clustered or nonclustered indexes - that is all.
SQL Server 实现主键和唯一键的方法是在这些列上放置索引。因此，如果没有索引，就不能有主键（或唯一约束）。
您可以告诉SQL Server 使用非聚集索引来实现这些索引。如果表上只有非聚集索引（或根本没有索引），则您有一个堆。这是您真正想要的，这是非常罕见的。
仅仅因为表具有聚集索引，这绝不表明表中的行将按此类索引定义的“顺序”返回 - 事实上，行通常返回该命令是一个实施怪癖。
实际的代码是：
The means by which SQL Server implements Primary and Unique keys is by placing an index on those columns. So you cannot have a Primary Key (or Unique constraint) without an index.
You can tell SQL Server to use a nonclustered index to implement these indexes. If there are only nonclustered indexes on a table (or no indexes at all), you have a heap. It's pretty rare that this is what you actually want.
Just because a table has a clustered index, this in no way indicates that the rows of the table will be returned in the "order" defined by such an index - the fact that the rows are usually returned in that order is an implementation quirk.
And the actual code would be:
“我不想对所有表格进行排序”是什么意思？如果这意味着您希望行按照输入的顺序显示，那么只有一种方法可以保证它：有一个存储该顺序的字段（或者如果您没有大量交易，则存储时间） 。在这种情况下，您将希望在该字段上拥有聚集索引以获得最佳性能。
您最终可能会在 autonumber_or_timestamp 字段上获得非聚集 PK（如 ProductId）和聚集唯一索引，以实现最佳性能。
但这实际上取决于您尝试建模的现实，而您的问题包含的相关信息太少。数据库设计不是抽象思维。
What does " I dont want all my tables to be sorted" mean ? If it means that you want the rows to appear in the order where they've been entered, there's only one way to garantee it: have a field that stores that order (or the time if you don't have a lot of transactions). And in that case, you will want to have a clustered index on that field for best performance.
You might end up with a non clustered PK (like the productId) AND a clustered unique index on your autonumber_or_timestamp field for max performance.
But that's really depending on the reality your're trying to model, and your question contains too little information about this. DB design is NOT abstract thinking.