CREATE STATISTICS (U-SQL)
U-SQL provides the
CREATE STATISTICS statement to create query optimization statistics on a column of a table.
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.
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 EXISTSclause has been specified. If the
IF NOT EXISTSclause has been specified and the statistics already exists and the user has at least enumeration permissions, the
CREATE STATISTICSstatement will silently complete without action. If the user has no enumeration permission, an error is raised.
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.
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.
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
- 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
USE TestReferenceDB; CREATE STATISTICS IF NOT EXISTS ordersStats ON dbo.Orders(OrderID) WITH FULLSCAN;