Table of Contents

Class ObjectSchema.HashIndex

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

sys.hash_indexes (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Shows the current hash indexes and the hash index properties. Hash indexes are supported only on In-Memory OLTP (In-Memory Optimization).
The sys.hash_indexes view contains the same columns as the sys.indexes view and an additional column named bucket_count. For more information about the other columns in the sys.hash_indexes view, see sys.indexes (Transact-SQL).


See sys.hash_indexes.

[Table(Schema = "sys", Name = "hash_indexes", IsView = true)]
public class ObjectSchema.HashIndex
Inheritance
ObjectSchema.HashIndex
Extension Methods

Properties

AllowPageLocks

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

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

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?

BucketCount

Count of hash buckets for hash indexes.

For more information about the bucket_count value, including guidelines for setting the value, see CREATE TABLE (Transact-SQL).

[Column("bucket_count")]
[NotNull]
public int BucketCount { get; set; }

Property Value

int

CompressionDelay

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

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

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

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

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

0 = Index does not have a filter.

[Column("has_filter")]
[Nullable]
public bool? HasFilter { get; set; }

Property Value

bool?

IgnoreDupKey

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

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

1 = Index is disabled.

0 = Index is not disabled.

[Column("is_disabled")]
[Nullable]
public bool? IsDisabled { get; set; }

Property Value

bool?

IsHypothetical

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

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

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

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

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

ID of the object to which this index belongs.

[Column("object_id")]
[NotNull]
public int ObjectID { get; set; }

Property Value

int

OptimizeForSequentialKey

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

SuppressDupKeyMessages

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

TypeColumn

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 TypeColumn { get; set; }

Property Value

byte

TypeDesc

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