CREATE INDEX#

Creates an index on a view in the virtual database. Underlying database system handling physical objects must support indexes for this feature to be available. For example, if a view is defined in a virtual database dependent on Apache Spark engine, then the index creation is not possible because Apache Spark does not support indexes.

Syntax#

CREATE [ UNIQUE ] [ CLUSTERED | non-clustered ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
[;]


<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    view_name
}

Arguments#

UNIQUE

Creates a unique index on a view. A unique index is one in which no two rows are permitted to have the same index key value.

The source Database Engine may not allow creating of a unique index on columns that already include duplicate values. Please refer to documentation of the respective system.

CLUSTERED

Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a backing table. The bottom, or leaf, level of the clustered index contains the actual data rows of the backing table. A 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 unique clustered index must be created on a view before any other indexes can be defined on the same view.

Create the clustered index before creating any non-clustered indexes. Existing non-clustered indexes on backing table are rebuilt when a clustered index is created.

If CLUSTERED is not specified, a non-clustered index is created.

non-clustered

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

If not otherwise specified, the default index type is non-clustered.

<!– VIRTUAL

Creates a virtual-only index that will not propagate to data source, also known as a metadata-only index. –>

index_name

Is the name of the index. Index names must be unique within a view but do not have to be unique within a database. Index names must follow the rules of identifiers.

column

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

Up to 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same view. The maximum allowable size of the combined index values depends on the system that handles physical objects, eg.: if connection points to MS SQL Server instance, the MS SQL server rules apply.

Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

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

INCLUDE (column [ ,… n ] ) Specifies the non-key columns to be added to the leaf level of the non-clustered index. The non-clustered index can be unique or non-unique.

<!– WHERE <filter_predicate> Creates a filtered index by specifying which rows to include in the index. The filtered index must be a non-clustered index on a view. Creates filtered statistics for the data rows in the filtered index.

The filter predicate uses simple comparison logic and cannot 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 and IS NOT NULL operators instead. –>

object ::=

Is the fully qualified or non-fully qualified object to be indexed.

database_name

Is the name of the virtual database.

schema_name

Is the name of the schema to which the view belongs.

view_name

Is the name of the view to be indexed.

Permissions#

Requires ALTER permission on the view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Metadata#

To view information on existing indexes, you can query the sys.indexes catalog view.

Examples#

Create a simple non-clustered index

The following examples create a non-clustered index on the VendorID column of the ProductVendor view.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);

Create a simple non-clustered composite index

The following example creates a non-clustered composite index on the SalesQuota and SalesYTD columns of the Sales.SalesPerson view.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

CREATE INDEX

Contents