TẠO INDEX TRONG SQL SERVER

To view Transact-SQL syntax for SQL Server năm trước and earlier, see Previous versions documentation.

Arguments

Creates a unique index on a table or view. A chất lượng index is one in which no two rows are permitted khổng lồ have the same index key value. A clustered index on a view must be unique.

The Database Engine doesn"t allow creating a chất lượng index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a quality index should be set to NOT NULL, because multiple null values are considered duplicates when a chất lượng index is created.

Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, màn chơi of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time.

A view with a unique clustered index is called an indexed view. Creating a unique clustered index on a view physically materializes the view. A chất lượng clustered index must be created on a view before any other indexes can be defined on the same view. For more information, see Create Indexed Views.

Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.

If CLUSTERED isn"t specified, a nonclustered index is created.

Note

Because the leaf cấp độ of a clustered index and the data pages are the same by definition, creating a clustered index và using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available & that they have enough empty space for the index.

In some cases creating a clustered index can enable previously disabled indexes. For more information, see Enable Indexes & Constraints and Disable Indexes và Constraints.

Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order.

Each table can have up lớn 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY & UNIQUE constraints, or explicitly with CREATE INDEX.

Xem thêm: Quy Luật Lượng Và Chất Cái Gì Biến Đổi Trước, Quy Luật Lượng

For indexed views, nonclustered indexes can be created only on a view that has a chất lượng clustered index already defined.

If not otherwise specified, the mặc định index type is nonclustered.

The name of the index. Index names must be chất lượng within a table or view, but don"t have to lớn be chất lượng within a database. Index names must follow the rules of identifiers.

The column or columns on which the index is based. Specify two or more column names lớn create a composite index on the combined values in the specified columns. List the columns to lớn be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name.

Up khổng lồ 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable kích thước of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. The limits are 16 columns and 900 bytes for versions before SQL Database & SQL Server năm nhâm thìn (13.x).

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image can"t be specified as key columns for an index. Also, a view definition can"t include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

You can create indexes on CLR user-defined type columns if the type supports binary ordering. You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and don"t perform data access operations. For more information about indexing CLR user-defined type columns, see CLR User-defined Types.

Determines the ascending or descending sort direction for the particular index column. The default is ASC.

Specifies the non-key columns lớn be added to the leaf level of the nonclustered index. The nonclustered index can be quality or non-unique.

Column names can"t be repeated in the INCLUDE list & can"t be used simultaneously as both key and non-key columns. Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table. For more information, see Create Indexes with Included Columns.

All data types are allowed except text, ntext, and image. Starting with SQL vps 2012 (11.x) & Azure SQL Database, if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types, the index can be built or rebuilt using the ONLINE option.

Computed columns that are deterministic & either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. For more information, see Indexes on Computed Columns.

For information on creating an XML index, see CREATE XML INDEX.

Creates a filtered index by specifying which rows lớn include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.

The filter predicate uses simple comparison súc tích and can"t reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL & IS NOT NULL operators instead.

Here are some examples of filter predicates for the Production.BillOfMaterials table:

WHERE StartDate > "20000101" and EndDate Filtered indexes don"t apply khổng lồ XML indexes and full-text indexes. For quality indexes, only the selected rows must have quality index values. Filtered indexes don"t allow the IGNORE_DUP_KEY option.

Xem thêm: Công Xã Paris Tồn Tại Trong Bao Nhiêu Ngày ? Please Wait

Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name specifies the column against which a partitioned index will be partitioned. This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name isn"t restricted to the columns in the index definition. Any column in the base table can be specified, except when partitioning a unique index, column_name must be chosen from among those used as the unique key. This restriction allows the Database Engine lớn verify uniqueness of key values within a single partition only.