Define Database Indexes

Apr 07, 2022
8 minutes to read

Indexes allow a database to find data in a table without scanning the entire table. You can create indexes on a single property (column) or a combination of properties (columns).

Note

Attributes described in this topic do not affect existing database schemas, they work only if you create a new database table.

If you have an existing database table, use your database server tools to create indexes.

Index for a Single Column (Property)

The Indexed attribute indicates that a table (or another form of data store) has an index for a decorated property (column).

C#
VB.NET

csharp

public class User : XPObject {
    [Indexed]
    public int UserId {
        get { return fUserId; }
        set { SetPropertyValue(nameof(UserId), ref fUserId, value); }
    }
    int fUserId;

    public string UserName {
        get { return fUserName; }
        set { SetPropertyValue(nameof(UserName), ref fUserName, value); }
    }
    string fUserName;
}

Public Class User
    Inherits XPObject
    <Indexed> _
    Public Property UserId() As Integer
        Get
            Return fUserId
        End Get
        Set(ByVal value as Integer)
            SetPropertyValue(NameOf(UserId), fUserId, value)
        End Set
    End Property
    Private fUserId As Integer

    Public Property UserName() As String
        Get
            Return fUserName
        End Get
        Set(ByVal value as String)
            SetPropertyValue(NameOf(UserName), fUserName, value)
        End Set
    End Property
    Private fUserName As String

End Class

Index Uniqueness

You can apply the IndexedAttribute attribute with Unique = true to a persistent property to guarantee the values in the corresponding column are unique:

C#
VB.NET

csharp

public class User : XPObject {
    [Indexed(Unique = true)]
    public int UserId {
        get { return fUserId; }
        set { SetPropertyValue(nameof(UserId), ref fUserId, value); }
    }
    int fUserId;

    public string UserName {
        get { return fUserName; }
        set { SetPropertyValue(nameof(UserName), ref fUserName, value); }
    }
    string fUserName;
}

Public Class User
    Inherits XPObject
    <Indexed(Unique:=True)> _
    Public Property UserId() As Integer
        Get
            Return fUserId
        End Get
        Set(ByVal value as Integer)
            SetPropertyValue(NameOf(UserId), fUserId, value)
        End Set
    End Property
    Private fUserId As Integer

    Public Property UserName() As String
        Get
            Return fUserName
        End Get
        Set(ByVal value as String)
            SetPropertyValue(NameOf(UserName), fUserName, value)
        End Set
    End Property
    Private fUserName As String

End Class

Composite Index

XPO allows you to define a multi-column index in a persistent object.

C#
VB.NET

csharp

using DevExpress.Xpo;

public class Person : XPObject {
    // Make a composite index for the LastName, FirstName, and BirthDate columns.
    // The combination of these properties is unique.
    [Indexed("FirstName;BirthDate", Unique=true)]
    public string LastName {
        get { return fLastName; }
        set { SetPropertyValue(nameof(LastName), ref fLastName, value); }
    }
    string fLastName = string.Empty;

    public string FirstName {
        get { return fFirstName; }
        set { SetPropertyValue(nameof(FirstName), ref fFirstName, value); }
    }
    string fFirstName = string.Empty;

    public DateTime BirthDate {
        get { return fBirthDate; }
        set { SetPropertyValue(nameof(BirthDate), ref fBirthDate, value); }
    }
    DateTime fBirthDate;

}

Imports DevExpress.Xpo

Public Class Person
    Inherits XPObject

    ' Make a composite index for the LastName, FirstName, and BirthDate columns.
    ' The combination of these properties is unique.
    <Indexed("FirstName;BirthDate", Unique:=True)> _
    Public Property LastName() As String
        Get
            Return fLastName
        End Get
        Set(ByVal value as String)
            SetPropertyValue(NameOf(LastName), fLastName, value)
        End Set
    End Property
    Private fLastName As String = String.Empty

    Public Property FirstName() As String
        Get
            Return fFirstName
        End Get
        Set(ByVal value as String)
            SetPropertyValue(NameOf(FirstName), fFirstName, value)
        End Set
    End Property
    Private fFirstName As String = String.Empty

    Public Property BirthDate() As DateTime
        Get
            Return fBirthDate
        End Get
        Set(ByVal value as DateTime)
            SetPropertyValue(NameOf(BirthDate), fBirthDate, value)
        End Set
    End Property
    Private fBirthDate As DateTime

End Class

Note

XPO does not support tables with multi-column (compound) keys or indexes in ASE databases. To avoid exceptions when connecting to ASE databases containing these tables, use one-column keys or indexes.

Apply an Attribute to an Object (Create One-Column or Composite Indexes)

To define multiple non-unique database indexes, use IndicesAttribute.

Apply this attribute to a persistent class to specify database indexes to be created in the database table associated with the class. The Indices attribute allows you to use a single attribute declaration to specify multiple non-unique indexes for the current table:

C#
VB.NET

csharp

[Indices("Name", "Name;Age", "Age;ChildCount")]
public class Person : XPObject {
    [Size(32)]
    public String Name {
        get { return fName; }
        set { SetPropertyValue(nameof(Name), ref fName, value); }
    }
    String fName;

    [Indexed(Unique = true), Size(64)]
    public String FullName {
        get { return fFullName; }
        set { SetPropertyValue(nameof(FullName), ref fFullName, value); }
    }
    String fFullName;

    public int Age {
        get { return fAge; }
        set { SetPropertyValue(nameof(Age), ref fAge, value); }
    }
    int fAge;

    public int ChildCount {
        get { return fChildCount; }
        set { SetPropertyValue(nameof(ChildCount), ref fChildCount, value); }
    }
    int fChildCount;

}

<Indices("Name", "Name;Age", "Age;ChildCount")> _
Public Class [Person]
    Inherits XPObject
    <Size(32)> _
    Public Property Name() As String
        Get
            Return fName
        End Get
        Set(ByVal value as String)
            SetPropertyValue(NameOf(Name), fName, value)
        End Set
    End Property
    Private fName As String

    <Indexed(Unique := True), Size(64)> _
    Public Property FullName() As String
        Get
            Return fFullName
        End Get
        Set(ByVal value as String)
            SetPropertyValue(NameOf(FullName), fFullName, value)
        End Set
    End Property
    Private fFullName As String

    Public Property Age() As Integer
        Get
            Return fAge
        End Get
        Set(ByVal value as Integer)
            SetPropertyValue(NameOf(Age), fAge, value)
        End Set
    End Property
    Private fAge As Integer

    Public Property ChildCount() As Integer
        Get
            Return fChildCount
        End Get
        Set(ByVal value as Integer)
            SetPropertyValue(NameOf(ChildCount), fChildCount, value)
        End Set
    End Property
    Private fChildCount As Integer

End Class

With this code in place, the database table corresponding to Person has the following indexes.

A non-unique index over the Name column.
A non-unique multi-column index over the Name and Age columns.
A non-unique multi-column index over the Age and ChildCount columns.
A unique index over the FullName column.

Include XPO’s Service Columns into Indexes to Enable Unique Value Constraints

The Indexed attribute can include XPO’s service columns (ObjectType and GCRecord) for the following purposes:

Allow persistent objects of different types have data fields with the same values. The data records remain unique against the class type (stored in the ObjectType column).
Allow a record to have the same field values as the deleted records have. This is helpful when the deferred deletion is enabled: the “deleted” records are not physically deleted and have non empty values in the GCRecord column.

Note

When a single table is created for each class in an inheritance hierarchy, you can add the IndexedAttribute/IndicesAttribute involving the service ObjectType and GCRecord columns only in the base persistent class, because XPO creates these columns only in the base table.

Steps to implement:

If a data property must be unique against the object type, decorate it with the IndexedAttribute attribute and specify “ObjectType” and Unique = true as parameters.
If a data property must be unique without taking into account deleted records, decorate it with the IndexedAttribute attribute taking “GCRecord” and Unique = true as parameters.

C#
VB.NET

csharp

using System;
using DevExpress.Xpo;
using System.ComponentModel;

namespace ConsoleApplication1 {
    public class BasePersistentClass : XPObject {
        public BasePersistentClass(Session session) : base(session) { }

        [Indexed("ObjectType", Unique = true)]
        public string UniqueAgainstObjectTypeInBaseClass { get; set; }

        [Indexed("GCRecord", Unique = true)]
        public string UniqueAgainstGCRecordInBaseClass { get; set; }
    }
    public class DerivedPersistentClass : BasePersistentClass {
        public DerivedPersistentClass(Session session) : base(session) { }

        [Indexed("ObjectTypeCopy", Unique = true)]
        public string UniqueAgainstObjectTypeInDerivedClass { get; set; }

        [Indexed("GCRecordCopy", Unique = true)]
        public string UniqueAgainstGCRecordInDerivedClass { get; set; }

        // To add uniqueness on the service columns in the derived class, 
        // you should declare additional *persistent* clone-properties 
        // that will return the value of corresponding source property.
        [Persistent, Browsable(false)]
        protected XPObjectType ObjectTypeCopy {
            get { return Session.GetObjectType(this); }
        }
        [Persistent, Browsable(false)]
        protected int? GCRecordCopy {
            get { return GetPropertyValue<int?>("GCRecord"); }
        }
    }
}

Imports Microsoft.VisualBasic
Imports System
Imports DevExpress.Xpo
Imports System.ComponentModel

Namespace ConsoleApplication1
    Public Class BasePersistentClass
        Inherits XPObject
        Public Sub New(ByVal session As Session)
            MyBase.New(session)
        End Sub

        Private privateUniqueAgainstObjectTypeInBaseClass As String
        <Indexed("ObjectType", Unique := True)> _
        Public Property UniqueAgainstObjectTypeInBaseClass() As String
            Get
                Return privateUniqueAgainstObjectTypeInBaseClass
            End Get
            Set(ByVal value As String)
                privateUniqueAgainstObjectTypeInBaseClass = value
            End Set
        End Property

        Private privateUniqueAgainstGCRecordInBaseClass As String
        <Indexed("GCRecord", Unique := True)> _
        Public Property UniqueAgainstGCRecordInBaseClass() As String
            Get
                Return privateUniqueAgainstGCRecordInBaseClass
            End Get
            Set(ByVal value As String)
                privateUniqueAgainstGCRecordInBaseClass = value
            End Set
        End Property
    End Class
    Public Class DerivedPersistentClass
        Inherits BasePersistentClass
        Public Sub New(ByVal session As Session)
            MyBase.New(session)
        End Sub

        Private privateUniqueAgainstObjectTypeInDerivedClass As String
        <Indexed("ObjectTypeCopy", Unique := True)> _
        Public Property UniqueAgainstObjectTypeInDerivedClass() As String
            Get
                Return privateUniqueAgainstObjectTypeInDerivedClass
            End Get
            Set(ByVal value As String)
                privateUniqueAgainstObjectTypeInDerivedClass = value
            End Set
        End Property

        Private privateUniqueAgainstGCRecordInDerivedClass As String
        <Indexed("GCRecordCopy", Unique := True)> _
        Public Property UniqueAgainstGCRecordInDerivedClass() As String
            Get
                Return privateUniqueAgainstGCRecordInDerivedClass
            End Get
            Set(ByVal value As String)
                privateUniqueAgainstGCRecordInDerivedClass = value
            End Set
        End Property

        ' To add uniqueness on the service columns in the derived class, 
        ' you should declare additional *persistent* clone-properties 
        ' that will return the value of corresponding source property.
        <Persistent, Browsable(False)> _
        Protected ReadOnly Property ObjectTypeCopy() As XPObjectType
            Get
                Return Session.GetObjectType(Me)
            End Get
        End Property
        <Persistent, Browsable(False)> _
        Protected ReadOnly Property GCRecordCopy() As Integer?
            Get
                Return GetPropertyValue(Of Integer?)("GCRecord")
            End Get
        End Property
    End Class
End Namespace

Note

Microsoft Access skips NULL values when checking value uniqueness.