Creating SQL Server Indexes

Article
04/01/2012

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

The SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider and SQL Server support ascending order on values in the index. The SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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 SQL Server Native Client OLE DB provider 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.

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);
    }

Share via

Creating SQL Server Indexes

See Also

Concepts

Additional resources