Index Element (DTA)

  • Article

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 Performing 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 SQL-92 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 SQL-92 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 SQL-92 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).

For more information about the attributes you must set if you are working with indexes on computed columns or indexed views, see SET Options That Affect Results.

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

Reference

XML Input File Reference (DTA)

Help and Information

Getting SQL Server 2005 Assistance