Table of Contents

Class XmlSchema.XmlIndex

Namespace
LinqToDB.Tools.DataProvider.SqlServer.Schemas
Assembly
linq2db.Tools.dll

sys.xml_indexes (Transact-SQL)

Applies to: √ SQL Server

Returns one row per XML index.


See sys.xml_indexes.

[Table(Schema = "sys", Name = "xml_indexes", IsView = true)]
public class XmlSchema.XmlIndex
Inheritance
XmlSchema.XmlIndex
Inherited Members
Extension Methods

Properties

AllowPageLocks

allow_page_locks NULL bit

1 = Index allows page locks.

0 = Index does not allow page locks.

Always 0 for clustered columnstore indexes. 
[Column("allow_page_locks")]
[Nullable]
public bool? AllowPageLocks { get; set; }

Property Value

bool?

AllowRowLocks

allow_row_locks NULL bit

1 = Index allows row locks.

0 = Index does not allow row locks.

Always 0 for clustered columnstore indexes. 
[Column("allow_row_locks")]
[Nullable]
public bool? AllowRowLocks { get; set; }

Property Value

bool?

AutoCreated

auto_created NULL bit

1 = Index was created by the automatic tuning.

0 = Index was created by the user.

Applies to: Azure SQL Database 
[Column("auto_created")]
[Nullable]
public bool? AutoCreated { get; set; }

Property Value

bool?

CompressionDelay

compression_delay NOT NULL int

> 0 = Columnstore index compression delay specified in minutes.

NULL = Columnstore index rowgroup compression delay is managed automatically. 
[Column("compression_delay")]
[NotNull]
public int CompressionDelay { get; set; }

Property Value

int

DataSpaceID

data_space_id NOT NULL int

ID of the data space for this index. Data space is either a filegroup or partition scheme.

0 = object_id is a table-valued function or in-memory index. 
[Column("data_space_id")]
[NotNull]
public int DataSpaceID { get; set; }

Property Value

int

FillFactor

fill_factor NOT NULL tinyint

> 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value

Always 0 for clustered columnstore indexes. 
[Column("fill_factor")]
[NotNull]
public byte FillFactor { get; set; }

Property Value

byte

FilterDefinition

filter_definition NULL nvarchar(max)

Expression for the subset of rows included in the filtered index.

NULL for heap, non-filtered index, or insufficient permissions on the table. 
[Column("filter_definition")]
[Nullable]
public string? FilterDefinition { get; set; }

Property Value

string

HasFilter

has_filter NOT NULL bit

1 = Index has a filter and only contains rows that satisfy the filter definition.

0 = Index does not have a filter. 
[Column("has_filter")]
[NotNull]
public bool HasFilter { get; set; }

Property Value

bool

IgnoreDupKey

ignore_dup_key NULL bit

1 = IGNORE_DUP_KEY is ON.

0 = IGNORE_DUP_KEY is OFF. 
[Column("ignore_dup_key")]
[Nullable]
public bool? IgnoreDupKey { get; set; }

Property Value

bool?

IndexID

index_id NOT NULL int

ID of the index. index_id is unique only within the object.

0 = Heap

1 = Clustered index

> 1 = Nonclustered index 
[Column("index_id")]
[NotNull]
public int IndexID { get; set; }

Property Value

int

IsDisabled

is_disabled NULL bit

1 = Index is disabled.

0 = Index is not disabled. 
[Column("is_disabled")]
[Nullable]
public bool? IsDisabled { get; set; }

Property Value

bool?

IsHypothetical

is_hypothetical NULL bit

1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.

0 = Index is not hypothetical. 
[Column("is_hypothetical")]
[Nullable]
public bool? IsHypothetical { get; set; }

Property Value

bool?

IsPadded

is_padded NULL bit

1 = PADINDEX is ON.

0 = PADINDEX is OFF.

Always 0 for clustered columnstore indexes. 
[Column("is_padded")]
[Nullable]
public bool? IsPadded { get; set; }

Property Value

bool?

IsPrimaryKey

is_primary_key NULL bit

1 = Index is part of a PRIMARY KEY constraint.

Always 0 for clustered columnstore indexes. 
[Column("is_primary_key")]
[Nullable]
public bool? IsPrimaryKey { get; set; }

Property Value

bool?

IsUnique

is_unique NULL bit

1 = Index is unique.

0 = Index is not unique.

Always 0 for clustered columnstore indexes. 
[Column("is_unique")]
[Nullable]
public bool? IsUnique { get; set; }

Property Value

bool?

IsUniqueConstraint

is_unique_constraint NULL bit

1 = Index is part of a UNIQUE constraint.

Always 0 for clustered columnstore indexes. 
[Column("is_unique_constraint")]
[Nullable]
public bool? IsUniqueConstraint { get; set; }

Property Value

bool?

Name

name NULL sysname

Name of the index. name is unique only within the object.

NULL = Heap 
[Column("name")]
[Nullable]
public string? Name { get; set; }

Property Value

string

Object

objects (sys.objects)

[Association(ThisKey = "ObjectID", OtherKey = "ObjectID", CanBeNull = false)]
public ObjectSchema.Object Object { get; set; }

Property Value

ObjectSchema.Object

ObjectID

object_id NOT NULL int

ID of the object to which this index belongs. 
[Column("object_id")]
[NotNull]
public int ObjectID { get; set; }

Property Value

int

OptimizeForSequentialKey

optimize_for_sequential_key NOT NULL bit

1 = Index has last-page insert optimization enabled.

0 = Default value. Index has last-page insert optimization disabled.

Applies to: SQL Server (Starting with SQL Server 2019 (15.x)), Azure SQL Database, and Azure SQL Managed Instance 
[Column("optimize_for_sequential_key")]
[NotNull]
public bool OptimizeForSequentialKey { get; set; }

Property Value

bool

PathID

path_id NULL int

NULL for all XML indexes except secondary selective XML index.

Else, the ID of the promoted path over which the secondary selective XML index is built. This value is the same value as path_id from sys.selective_xml_index_paths system view. 
[Column("path_id")]
[Nullable]
public int? PathID { get; set; }

Property Value

int?

SecondaryType

secondary_type NULL char(1)

Type description of secondary index:

P = PATH secondary XML index

V = VALUE secondary XML index

R = PROPERTY secondary XML index

NULL = Primary XML index 
[Column("secondary_type")]
[Nullable]
public string? SecondaryType { get; set; }

Property Value

string

SecondaryTypeDesc

secondary_type_desc NULL nvarchar(60)

Type description of secondary index:

PATH = PATH secondary XML index

VALUE = VALUE secondary XML index

PROPERTY = PROPERTY secondary xml indexes.

NULL = Primary XML index 
[Column("secondary_type_desc")]
[Nullable]
public string? SecondaryTypeDesc { get; set; }

Property Value

string

SuppressDupKeyMessages

suppress_dup_key_messages NOT NULL bit

1 = Index is configured to suppress duplicate key messages during an index rebuild operation.

0 = Index is not configured to suppress duplicate key messages during an index rebuild operation.

Applies to: SQL Server (Starting with SQL Server 2017 (14.x)), Azure SQL Database, and Azure SQL Managed Instance 
[Column("suppress_dup_key_messages")]
[NotNull]
public bool SuppressDupKeyMessages { get; set; }

Property Value

bool

Type

type NOT NULL tinyint

Type of index:

0 = Heap

1 = Clustered rowstore (B-tree)

2 = Nonclustered rowstore (B-tree)

3 = XML

4 = Spatial

5 = Clustered columnstore index. Applies to: SQL Server 2014 (12.x) and later.

6 = Nonclustered columnstore index. Applies to: SQL Server 2012 (11.x) and later.

7 = Nonclustered hash index. Applies to: SQL Server 2014 (12.x) and later. 
[Column("type")]
[NotNull]
public byte Type { get; set; }

Property Value

byte

TypeDesc

type_desc NULL nvarchar(60)

Description of index type:

HEAP

CLUSTERED

NONCLUSTERED

XML

SPATIAL

CLUSTERED COLUMNSTORE - Applies to: SQL Server 2014 (12.x) and later.

NONCLUSTERED COLUMNSTORE - Applies to: SQL Server 2012 (11.x) and later.

NONCLUSTERED HASH : NONCLUSTERED HASH indexes are supported only on memory-optimized tables. The sys.hash_indexes view shows the current hash indexes and the hash properties. For more information, see sys.hash_indexes (Transact-SQL). Applies to: SQL Server 2014 (12.x) and later. 
[Column("type_desc")]
[Nullable]
public string? TypeDesc { get; set; }

Property Value

string

UsingXmlIndexID

using_xml_index_id NULL int

NULL = Primary XML index.

Nonnull = Secondary XML index.

Nonnull is a self-join reference to the primary XML index. 
[Column("using_xml_index_id")]
[Nullable]
public int? UsingXmlIndexID { get; set; }

Property Value

int?

XmlIndeXType

xml_index_type NULL tinyint

Index type:

0 = Primary XML index

1 = Secondary XML index

2 = Selective XML index

3 = Secondary selective XML index 
[Column("xml_index_type")]
[Nullable]
public byte? XmlIndeXType { get; set; }

Property Value

byte?

XmlIndexTypeDescription

xml_index_type_description NULL nvarchar(60)

Description of index type:

PRIMARY_XML

Secondary XML Index

Selective XML Index

Secondary Selective XML index 
[Column("xml_index_type_description")]
[Nullable]
public string? XmlIndexTypeDescription { get; set; }

Property Value

string