Table of Contents
DODS generator can create statistics statements. New element in doml STATISTICS is introduced. It creates a histogram and associated density groups (collections) over the supplied column or set of columns of a table or indexed view. String summary statistics are also created on statistics built on char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text, and ntext columns. The query optimizer uses this statistical information to choose the most efficient plan for retrieving or updating data. Up-to-date statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan.
Attributes and subelements
id - is the name of the statistics group to create. Statistics names must comply with the rules for identifiers and must be unique to the table or view on which they are created.
statisticsColumn - is subelement and it is the column or set of columns on which to create statistics. Any column that can be specified as an index key can also be specified for statistics, with the following exceptions: xml columns cannot be specified. The maximum allowable size of the combined column values can exceed the 900-byte limit that is imposed on the index key value. Computed columns can be specified only if the ARITHABORT and QUOTED_IDENTIFIER database options are set to ON. CLR user-defined type columns can be specified if the type supports binary ordering. Computed columns defined as method invocations off a user-defined type column can be specified if the methods are marked deterministic.
fullScan - Specifies that all rows in table or view should be read to gather the statistics. Specifying FULLSCAN provides the same behavior as SAMPLE 100 PERCENT. This option cannot be used with the SAMPLE option.
sample(sampleNo,sampleType) - Specifies that a percentage, or a specified number of rows, of the data should be read by using random sampling to gather the statistics. Number must be an integer. If PERCENT is specified, number should be from 0 through 100; if ROWS is specified, number can be from 0 to the n total rows.
noRecompute - Specifies that the Database Engine should not automatically recompute statistics. If this option is specified, the Database Engine continues to use previously created (old) statistics, even as the data changes. The statistics are not automatically updated and maintained by the Database Engine. This may produce suboptimal plans.
Only the table owner can create statistics on that table. The owner of a table can create a statistics group (collection) at any time, whether or not there is data in the table. If the AUTO_UPDATE_STATISTICS database option is set to ON (this is the default setting) and the NORECOMPUTE clause is not specified, the Database Engine will automatically update any statistics that are manually created. CREATE STATISTICS can be executed on an indexed view. Statistics on indexed views are used by the optimizer only if the view is directly referenced in the query and the NOEXPAND hint is specified for the view. Otherwise, statistics are derived from the underlying tables before the indexed view is substituted into the query plan. This substitution is supported only on Microsoft SQL Server 2005 Enterprise and Developer editions.