Index Element (DTA)

Index Element (DTA)

 

Applies To: SQL Server 2016

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

  
<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>  

Index attributeData typeDescription
ClusteredbooleanOptional. Specifies a clustered index. Set to either "true" or "false", for example:

 <Index Clustered="true">

By default, this attribute is set to "false".
UniquebooleanOptional. Specifies a unique index. Set to either "true" or "false", for example:

 <Index Unique="true">

By default, this attribute is set to "false".
OnlinebooleanOptional. 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.
IndexSizeInMBdoubleOptional. Specifies the maximum size of the index in megabytes, for example:

 <Index IndexSizeInMB="873.75">

No default setting.
NumberOfRowsintegerOptional. Simulates different index sizes, which effectively simulates different table sizes, for example:

 <Index NumberOfRows="3000">

No default setting.
QUOTED_IDENTIFIERbooleanOptional. 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).
ARITHABORTbooleanOptional. 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
booleanOptional. 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_NULLSbooleanOptional. 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_PADDINGbooleanOptional. 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_WARNINGSbooleanOptional. 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_ROUNDABORTbooleanOptional. 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).
CharacteristicDescription
Data type and lengthNone.
Default valueNone.
OccurrenceRequired once for each Create or Drop element if no other physical design structure is specified with either the Statistics or the Heap elements.
RelationshipElements
Parent elementCreate Element (DTA)

 Drop Element. For more information, see the Database Engine Tuning Advisor XML schema.
Child elementsName 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.

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

XML Input File Reference (Database Engine Tuning Advisor)

Community Additions

ADD
Show:
© 2016 Microsoft