Share via

CREATE STATISTICS (U-SQL)

  • Article

Summary

U-SQL provides the CREATE STATISTICS statement to create query optimization statistics on a column of a table.

Syntax

Create_Statistics_Statement :=                                                                           
    'CREATE' 'STATISTICS' ['IF' 'NOT' 'EXISTS']  
    Statistic_Name  
    'ON' Table_Name '(' Column_Name ')'  
    'WITH' 'FULLSCAN'.

Statistic_Name := 
    Quoted_or_Unquoted_Identifier.
 
Table_Name := 
    Identifier.
  
Column_Name := 
    Quoted_or_Unquoted_Identifier.

Remarks

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

  • IF NOT EXISTS
    If the statistics of the given name already exists on the specified table and the user has access to it, an error is raised unless the IF NOT EXISTS clause has been specified. If the IF NOT EXISTS clause has been specified and the statistics already exists and the user has at least enumeration permissions, the CREATE STATISTICS statement will silently complete without action. If the user has no enumeration permission, an error is raised.

  • Table_Name
    Specifies the table on which the statistic is being created. 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 a statistic on it, an error is raised.

  • Column_Name
    Specifies the column for which the statistics is being computed inside the parenthesis. Currently only one column can be specified. If the specified table or column does not exist or the user does not have access to it, an error is raised.

  • WITH FULLSCAN
    The statistics are computed by scanning all rows. To provide compatibility with Microsoft SQL Server’s CREATE STATISTICS command, U-SQL currently requires the specification of WITH FULLSCAN.

Examples

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

The following example creates a statistics on the OrderID column within the table Orders in TestReferenceDB.

USE TestReferenceDB; 
CREATE STATISTICS IF NOT EXISTS ordersStats ON dbo.Orders(OrderID) WITH FULLSCAN;

See Also