Index Element (DTA)

Article
06/13/2017

Contains information about an index that you want to create or drop for a user-specified configuration.

Syntax

  
<Recommendation>  
  <Create>  
    <Index [Clustered | Unique | Online | IndexSizeInMB | NumberOfRows             | QUOTED_IDENTIFIER | ARITHABORT | CONCAT_NULL_YIELDS_NULL             | ANSI_NULLS | ANSI_PADDING | ANSI_WARNINGS  
            | NUMERIC_ROUNDABORT]  
     ...code removed here...  
    </Index>

Element Attributes

Index attribute	Data type	Description
`Clustered`	`boolean`	Optional. Specifies a clustered index. Set to either "true" or "false", for example: `<Index Clustered="true">` By default, this attribute is set to "false".
`Unique`	`boolean`	Optional. Specifies a unique index. Set to either "true" or "false", for example: `<Index Unique="true">` By default, this attribute is set to "false".
`Online`	`boolean`	Optional. Specifies an index that can perform operations while the server is online, which requires temporary disk space. Set to either "true" or "false", for example: `<Index Online="true">` By default, this attribute is set to "false". For more information, see Perform Index Operations Online.
`IndexSizeInMB`	`double`	Optional. Specifies the maximum size of the index in megabytes, for example: `<Index IndexSizeInMB="873.75">` No default setting.
`NumberOfRows`	`integer`	Optional. Simulates different index sizes, which effectively simulates different table sizes, for example: `<Index NumberOfRows="3000">` No default setting.
`QUOTED_IDENTIFIER`	`boolean`	Optional. Causes Microsoft SQL Server to follow the ISO rules regarding quotation marks delimiting identifiers and literal strings. This attribute must be turned on if the index is on a computed column or a view. For example, the following syntax sets this attribute on: `<Index QUOTED_IDENTIFIER [...]>` By default this attribute is turned off. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
`ARITHABORT`	`boolean`	Optional. Causes a query to terminate when an overflow or divide-by-zero error occurs during query execution. This attribute must be turned on if the index is on a computed column or a view. For example, the following syntax sets this attribute on: `<Index ARITHABORT [...]>` By default this attribute is turned off. For more information, see SET ARITHABORT (Transact-SQL).
`CONCAT_NULL_YIELDS_` `NULL`	`boolean`	Optional. Controls whether or not concatenation results are treated as null or empty string values. This attribute must be turned on if the index is on a computed column or a view. For example, the following syntax sets this attribute on: `<Index CONCAT_NULL_YIELDS_NULL [...]>` By default this attribute is turned off. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
`ANSI_NULLS`	`boolean`	Optional. Specifies ISO compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values. This attribute must be turned on if the index is on a computed column or a view. For example, the following syntax sets this attribute on: `<Index ANSI_NULLS [...]>` By default this attribute is turned off. For more information, see SET ANSI_NULLS (Transact-SQL).
`ANSI_PADDING`	`boolean`	Optional. Controls the way a column stores values shorter than its defined size. This attribute must be turned on if the index is on a computed column or a view. For example, the following syntax sets this attribute on: `<Index ANSI_PADDING [...]>` By default this attribute is turned off. For more information, see SET ANSI_PADDING (Transact-SQL).
`ANSI_WARNINGS`	`boolean`	Optional. Specifies ISO standard behavior for several error conditions. This attribute must be turned on if the index is on a computed column or a view. For example, the following syntax sets this attribute on: `<Index ANSI_WARNING [...]>` By default this attribute is turned off. For more information, see SET ANSI_WARNINGS (Transact-SQL).
`NUMERIC_ROUNDABORT`	`boolean`	Optional. Specifies the level of error reporting generated when rounding in an expression causes a loss of precision. This attribute must be off if the index is on a computed column or a view. The following syntax sets this attribute on: `<Index ANSI_WARNING [...]>` By default this attribute is turned off. For more information, see SET NUMERIC_ROUNDABORT (Transact-SQL).

Element Characteristics

Characteristic	Description
Data type and length	None.
Default value	None.
Occurrence	Required once for each `Create` or `Drop` element if no other physical design structure is specified with either the `Statistics` or the `Heap` elements.

Element Relationships

Relationship Elements

Parent element Create Element (DTA)

Drop Element. For more information, see the Database Engine Tuning Advisor XML schema.

Child elements Name Element for Index (DTA)

Column Element for Index (DTA)

PartitionScheme Element. For more information, see the Database Engine Tuning Advisor XML schema.

PartitionColumn Element. For more information, see the Database Engine Tuning Advisor XML schema.

Filegroup Element for Index (DTA)

NumberOfReferences Element. For more information, see the Database Engine Tuning Advisor XML schema.

PercentUsage Element. For more information, see the Database Engine Tuning Advisor XML schema.

Relationship	Elements
Parent element	Create Element (DTA) `Drop` Element. For more information, see the Database Engine Tuning Advisor XML schema.
Child elements	Name Element for Index (DTA) Column Element for Index (DTA) `PartitionScheme` Element. For more information, see the Database Engine Tuning Advisor XML schema. `PartitionColumn` Element. For more information, see the Database Engine Tuning Advisor XML schema. Filegroup Element for Index (DTA) `NumberOfReferences` Element. For more information, see the Database Engine Tuning Advisor XML schema. `PercentUsage` Element. For more information, see the Database Engine Tuning Advisor XML schema.

Example

For a usage example of this element, see the XML Input File Sample with User-specified Configuration (DTA) .

Share via