Creating SQL Server Indexes

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

SQLOLEDB 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, 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 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 table. SQL Server supports at most one clustered index on any table.

VARIANT_FALSE: SQLOLEDB attempts to create a nonclustered index on the SQL Server table.

DBPROP_INDEX_FILLFACTOR R/W: Read/write
Default: 0
Description: Specifies the percent of an index page used for storage. For more information, see CREATE INDEX in Microsoft SQL Server Transact-SQL and Utilities Reference.

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 table’s PRIMARY KEY constraint.  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);

    }

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.