Index Element (DTA)

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