CREATE INDEX (U-SQL)
This statement creates a clustered index with the given name on the specified table.
Create_Index_Statement := 'CREATE' 'CLUSTERED' 'INDEX' Quoted_or_Unquoted_Identifier 'ON' Identifier '(' Sort_Item_List ')' [Partition_Specification].
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.
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.
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.
- 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
CustomerID on an existing table called
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);