“聚集索引"和“按条款订购" [英] "Clustered index" and "Order by Clause"

问题描述

聚集索引和按子句排序之间有什么区别吗?

我必须从主表填充下拉列表，以下是查询.

I have to populate the Dropdown from the Master Table and following is the query.

Select Id, Name from Table Order by Name

对于上述任务，我应该使用按子句排序还是聚集索引?

Should I use the Order by Clause Or Clustered Index for the above task?

编辑

下面是表格的架构

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lookup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[lookup](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_lookup_ID] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[lookup]') AND name = N'IX_lookup_Name')
CREATE CLUSTERED INDEX [IX_lookup_Name] ON [dbo].[lookup]
(
    [Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

我在 Name 上也有一个聚集索引.但现在它没有显示在模式中.抱歉，我不知道为什么.

I have a clustered index on Name as well. But right now it is not showing in the schema. Sorry, I don't know why.

推荐答案

Apples and Oranges.聚集索引是一种存储选项.ORDER BY 是一个查询选项.如果您需要有序的结果，获得它们的唯一方法是将 ORDER BY 子句添加到您的查询中.时期.

Apples and Oranges. A clustered index is a storage option. An ORDER BY is a query option. If you need ordered results, the only way to get them is to add an ORDER BY clause to your query. Period.

索引可以帮助查询优化器生成更有效的计划，并利用索引作为满足 ORDER BY 要求的一种手段.但无论如何，索引的存在，无论是聚集的还是非聚集的，都不能保证结果的任何排序.

An index may help the query optimizer generate a more efficient plan and leverage the index as a means to satisfy the ORDER BY requirement. But by no means will the presence of an index, clustered or non-clustered, guarantee any ordering of a result.

所以你绝对需要在你的查询中使用 ORDER BY.您还可以考虑通过 Name 列索引来帮助查询.是否使用索引，取决于更多因素.您应该阅读设计索引和临界点.

So you absolutely need ORDER BY in your queries. You also may consider an index by Name column to help this the query. Whether an index will be used or not, it depends on many more factors. you should read Designing Indexes and The Tipping Point.

这篇关于“聚集索引"和“按条款订购"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！