Partager via

CREATE INDEX (U-SQL)

  • Article

Summary

This statement creates a clustered index with the given name on the specified table.

Syntax

Create_Index_Statement :=                                                                                
     'CREATE' 'CLUSTERED' 'INDEX' Quoted_or_Unquoted_Identifier
     'ON' Identifier '(' Sort_Item_List ')' [Partition_Specification].

Remarks

  • Quoted_or_Unquoted_Identifier
    Specifies the name of the index as either a quoted or unquoted identifier. The index name is unique in the context of the table, i.e., several tables could have indexes with the same name.

  • Identifier
    The table can be either specified with a fully qualified three-part name, a two-part name that refers to a table in the current database context, or a single name that refers to a table in the current database and schema context.

    If the table does not exist or the user does not have permissions to create an index on it, an error is raised. An error is also raised if the table already has a clustered index specified, since every table can only have one clustered index.

  • Sort_Item_List
    Specifies the column names and optional sort order that is used to perform the clustering of the data.

  • Partition_Specification
    The optional partition specification specifies how the index (and thus the table) will be partitioned.

Note that it is normally recommended to define the clustered index with the CREATE TABLE statement. While the index definition is optional as part of the table definition, no data can be inserted into the table until an index has been defined.

Examples

  • The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
  • The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.

The following example creates a Clustered Index on the columns OrderID and CustomerID on an existing table called Orders in TestReferenceDB. Orders will also be partitioned on column OrderDate. The example provides an alternative for the same table created in the example for CREATE TABLE (U-SQL): Creating a Table with Schema.

CREATE DATABASE IF NOT EXISTS TestReferenceDB;
USE TestReferenceDB;

DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderID int,
    CustomerID int,
    OrderDetailID int,
    OrderTotal double,
    OrderDate DateTime
);

CREATE CLUSTERED INDEX clx_OrderID_CustomerID 
ON TestReferenceDB.dbo.Orders(OrderID, CustomerID ASC)
PARTITIONED BY (OrderDate)
DISTRIBUTED BY HASH (OrderID, CustomerID);

See Also