Index Element (DTA)

Applies to: SQL Server

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.

Example

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

See Also

XML Input File Reference (Database Engine Tuning Advisor)