Creating SQL Server Indexes

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Download OLE DB driver

The OLE DB Driver for SQL Server exposes the IIndexDefinition::CreateIndex function, allowing consumers to define new indexes on SQL Server tables.

The OLE DB Driver for SQL Server creates table indexes as either indexes or constraints. SQL Server gives constraint-creation privilege to the table owner, database owner, and members of certain administrative roles. By default, only the table owner can create an index on a table. Therefore, the success or failure of CreateIndex depends not only on the application user's access rights but also on the type of index created.

Consumers specify the table name as a Unicode character string in the pwszName member of the uName union in the pTableID parameter. The eKind member of pTableID must be DBKIND_NAME.

The pIndexID parameter can be NULL, and if it is, the OLE DB Driver for SQL Server creates a unique name for the index. The consumer can capture the name of the index by specifying a valid pointer to a DBID in the ppIndexID parameter.

The consumer can specify the index name as a Unicode character string in the pwszName member of the uName union of the pIndexID parameter. The eKind member of pIndexID must be DBKIND_NAME.

The consumer specifies the column or columns participating in the index by name. For each DBINDEXCOLUMNDESC structure used in CreateIndex, the eKind member of the pColumnID must be DBKIND_NAME. The name of the column is specified as a Unicode character string in the pwszName member of the uName union in the pColumnID.

The OLE DB Driver for SQL Server and SQL Server support ascending order on values in the index. The OLE DB Driver for SQL Server returns E_INVALIDARG if the consumer specifies DBINDEX_COL_ORDER_DESC in any DBINDEXCOLUMNDESC structure.

CreateIndex interprets index properties as follows.

Property ID Description
DBPROP_INDEX_AUTOUPDATE R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_CLUSTERED R/W: Read/write

Default: VARIANT_FALSE

Description: Controls index clustering.

VARIANT_TRUE: The OLE DB Driver for SQL Server attempts to create a clustered index on the SQL Server table. SQL Server supports at most one clustered index on any table.

VARIANT_FALSE: The OLE DB Driver for SQL Server attempts to create a nonclustered index on the SQL Server table.
DBPROP_INDEX_FILLFACTOR R/W: Read/write

Default: 0

Description: Specifies the percentage of an index page used for storage. For more information, see CREATE INDEX.

The type of the variant is VT_I4. The value must be greater than or equal to 1 and less than or equal to 100.
DBPROP_INDEX_INITIALIZE R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_NULLCOLLATION R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_NULLS R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_PRIMARYKEY R/W: Read/write

Default: VARIANT_FALSE Description: Creates the index as a referential integrity, PRIMARY KEY constraint.

VARIANT_TRUE: The index is created to support the PRIMARY KEY constraint of the table. The columns must be nonnullable.

VARIANT_FALSE: The index is not used as a PRIMARY KEY constraint for row values in the table.
DBPROP_INDEX_SORTBOOKMARKS R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_TEMPINDEX R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_TYPE R/W: Read/write

Default: None

Description: The OLE DB Driver for SQL Server does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURRED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_UNIQUE R/W: Read/write

Default: VARIANT_FALSE

Description: Creates the index as a UNIQUE constraint on the participating column or columns.

VARIANT_TRUE: The index is used to uniquely constrain row values in the table.

VARIANT_FALSE: The index does not uniquely constrain row values.

In the provider-specific property set DBPROPSET_SQLSERVERINDEX, the OLE DB Driver for SQL Server defines the following data source information property.

Property ID Description
SSPROP_INDEX_XML Type: VT_BOOL (R/W)

Default: VARIANT_FALSE

Description: When this property is specified with a value of VARIANT_TRUE with IIndexDefinition::CreateIndex, it results in a primary xml index being created corresponding to the column being indexed. If this property is VARIANT_TRUE, cIndexColumnDescs should be 1, otherwise it is an error.

This example creates a primary key index:

// This CREATE TABLE statement shows the referential integrity and   
// PRIMARY KEY constraint on the OrderDetails table that will be created   
// by the following example code.  
//  
// CREATE TABLE OrderDetails  
// (  
//    OrderID      int      NOT NULL  
//    ProductID   int      NOT NULL  
//        CONSTRAINT PK_OrderDetails  
//        PRIMARY KEY CLUSTERED (OrderID, ProductID),  
//    UnitPrice   money      NOT NULL,  
//    Quantity   int      NOT NULL,  
//    Discount   decimal(2,2)   NOT NULL  
//        DEFAULT 0  
// )  
//  
HRESULT CreatePrimaryKey  
    (  
    IIndexDefinition* pIIndexDefinition  
    )  
    {  
    HRESULT             hr = S_OK;  
  
    DBID                dbidTable;  
    DBID                dbidIndex;  
    const ULONG         nCols = 2;  
    ULONG               nCol;  
    const ULONG         nProps = 2;  
    ULONG               nProp;  
  
    DBINDEXCOLUMNDESC   dbidxcoldesc[nCols];  
    DBPROP              dbpropIndex[nProps];  
    DBPROPSET           dbpropset;  
  
    DBID*               pdbidIndexOut = NULL;  
  
    // Set up identifiers for the table and index.  
    dbidTable.eKind = DBKIND_NAME;  
    dbidTable.uName.pwszName = L"OrderDetails";  
  
    dbidIndex.eKind = DBKIND_NAME;  
    dbidIndex.uName.pwszName = L"PK_OrderDetails";  
  
    // Set up column identifiers.  
    for (nCol = 0; nCol < nCols; nCol++)  
        {  
        dbidxcoldesc[nCol].pColumnID = new DBID;  
        dbidxcoldesc[nCol].pColumnID->eKind = DBKIND_NAME;  
  
        dbidxcoldesc[nCol].eIndexColOrder = DBINDEX_COL_ORDER_ASC;  
        }  
    dbidxcoldesc[0].pColumnID->uName.pwszName = L"OrderID";  
    dbidxcoldesc[1].pColumnID->uName.pwszName = L"ProductID";  
  
    // Set properties for the index. The index is clustered,  
    // PRIMARY KEY.  
    for (nProp = 0; nProp < nProps; nProp++)  
        {  
        dbpropIndex[nProp].dwOptions = DBPROPOPTIONS_REQUIRED;  
        dbpropIndex[nProp].colid = DB_NULLID;  
  
        VariantInit(&(dbpropIndex[nProp].vValue));  
  
        dbpropIndex[nProp].vValue.vt = VT_BOOL;  
        }  
    dbpropIndex[0].dwPropertyID = DBPROP_INDEX_CLUSTERED;  
    dbpropIndex[0].vValue.boolVal = VARIANT_TRUE;  
  
    dbpropIndex[1].dwPropertyID = DBPROP_INDEX_PRIMARYKEY;  
    dbpropIndex[1].vValue.boolVal = VARIANT_TRUE;  
  
    dbpropset.rgProperties = dbpropIndex;  
    dbpropset.cProperties = nProps;  
    dbpropset.guidPropertySet = DBPROPSET_INDEX;  
  
    hr = pIIndexDefinition->CreateIndex(&dbidTable, &dbidIndex, nCols,  
        dbidxcoldesc, 1, &dbpropset, &pdbidIndexOut);  
  
    // Clean up dynamically allocated DBIDs.  
    for (nCol = 0; nCol < nCols; nCol++)  
        {  
        delete dbidxcoldesc[nCol].pColumnID;  
        }  
  
    return (hr);  
    }  

See Also

Tables and Indexes