Creating SQL Server Indexes

SQLOLEDB exposes the IIndexDefinition::CreateIndex function, allowing consumers to define new indexes on Microsoft® SQL Server™ 2000 tables.

SQLOLEDB creates table indexes as either indexes or constraints. SQL Server 2000 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, CreateIndex success or failure 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, SQLOLEDB 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.

SQLOLEDB and SQL Server 2000 support ascending order on values in the index. SQLOLEDB 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: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED 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: SQLOLEDB attempts to create a clustered index on the SQL Server 2000 table. SQL Server 2000 supports at most one clustered index on any table. VARIANT_FALSE: SQLOLEDB attempts to create a nonclustered index on the SQL Server 2000 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: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_NULLCOLLATION	R/W: Read/write Default: None Description: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_NULLS	R/W: Read/write Default: None Description: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED 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: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_TEMPINDEX	R/W: Read/write Default: None Description: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED return value. The dwStatus member of the property structure indicates DBPROPSTATUS_BADVALUE.
DBPROP_INDEX_TYPE	R/W: Read/write Default: None Description: SQLOLEDB does not support this property. Attempts to set the property in CreateIndex cause a DB_S_ERRORSOCCURED 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.

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