索引sql server_SQL Server索引操作

索引sql server

In the previous articles of this series, we described the structure of the SQL Server tables and indexes, the main concepts that are used to describe the index and the basics and guidelines that are used to design the proper index. In this article, we will go through the operations that can be performed on the SQL Server indexes.

在本系列的前几篇文章中 ，我们描述了SQL Server表和索引的结构，用于描述索引的主要概念以及用于设计适当索引的基础和准则。 在本文中，我们将介绍可以在SQL Server索引上执行的操作。

创建索引 (Creating Indexes)

Before creating an index, it is better to follow the index design guidelines and best practices that are described in the previous article, to determine the columns that will participate in the index, the type of the created index, the suitable index options, such as the FillFactor or Sort in TempDB, and the storage location of that index.

在创建索引之前，最好遵循上一篇文章中描述的索引设计准则和最佳实践，以确定将参与索引的列，创建的索引的类型，合适的索引选项，例如在TempDB中的FillFactor或Sort，以及该索引的存储位置。

A SQL Server index can be created using the CREATE INDEX T-SQL statement or from the New Index dialog box using the SQL Server Management Studio tool, by providing

可以使用CREATE INDEX T-SQL语句或使用SQL Server Management Studio工具从“ 新建索引”对话框中创建SQL Server索引，方法是提供

• the index name,
  索引名称，
• the index type,
  索引类型，
• the uniqueness of the index key values,
  索引键值的唯一性，
• the name of the table on which the index will be created,
  将在其上创建索引的表的名称，
• list of columns that will participate in that index
  将参与该索引的列的列表
• and different index options such as the FillFactor, Sort in TempDB, drop the existing similar indexes
  和不同的索引选项（例如FillFactor，在TempDB中排序）删除现有的相似索引
• and the index filegroup and location
  以及索引文件组和位置

The CREATE INDEX T-SQL syntax below is used as a template to create a new SQL Server index. The absence of the UNIQUE option will not force the uniqueness of the index key values. In the unique index, no two rows are permitted to have the same index key value. If the type of the index is not specified in the CREATE INDEX T-SQL statement, a Non-Clustered index will be created.

下面的CREATE INDEX T-SQL语法用作创建新SQL Server索引的模板。 缺少UNIQUE选项不会强制索引键值的唯一性。 在唯一索引中，不允许两行具有相同的索引键值。 如果在CREATE INDEX T-SQL语句中未指定索引的类型，则将创建非聚集索引。

CREATE [UNIQUE] [ CLUSTERED | NONCLUSTERED ] INDEX [index_name] ON [TableName]
(
	[Column1] ASC,
	[Column2] ASC
) ON PRIMARY
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The same operation can be performed using SQL Server Management Studio, by browsing the table on which you need to create an index, right click on the Indexes node and choose New Index option, from which you can specify the type of the index to be created, as shown below:

使用SQL Server Management Studio，可以通过浏览需要在其上创建索引的表，右键单击“索引”节点并选择“ 新建索引”选项来执行相同的操作，从中可以指定要创建的索引的类型。 ， 如下所示：

From the General tab of the displayed New Index dialog box, the name of the table will be filled automatically, with an editable suggested name of the index. The type of the index will be also filled automatically from the provided types list, with the ability to specify the uniqueness of that index and list of columns that will be added to that index and the order of each column, as shown clearly below:

在显示的“新建索引”对话框的“ 常规”选项卡中，表的名称将自动填充，并带有建议的可编辑索引名称。 索引的类型也将从提供的类型列表中自动填充，并具有指定该索引的唯一性以及将添加到该索引的列列表以及每一列的顺序的功能，如下所示：

In the Options tab of the New Index dialog box, you can change the default values of the different index creation options, same as the ones specified in the WITH clause of the CREATE INDEX T-SQL statement, as shown below:

在“新建索引”对话框的“ 选项”选项卡中，可以更改不同索引创建选项的默认值，这些默认值与CREATE INDEX T-SQL语句的WITH子句中指定的选项相同，如下所示：

The storage location setting of the index, such as the filegroup and the partition schema in which the index will be stored, can be specified in the Storage tab of the New Index dialog box, as shown below:

可以在“新建索引”对话框的“ 存储”选项卡中指定索引的存储位置设置，例如将在其中存储索引的文件组和分区架构，如下所示：

From the Filter tab of the New Index dialog box, you can specify the WHERE condition of the filtered index, to select the data to be stored in the created filtered index, as shown below:

在“新建索引”对话框的“ 过滤器”选项卡上，可以指定过滤索引的WHERE条件，以选择要存储在创建的过滤索引中的数据，如下所示：