Creating SQL Server Indexes

SQLOLEDB exposes IIndexDefinition::CreateIndex allowing consumers to define new indexes on SQL Server tables.

SQLOLEDB creates table indexes as either indexes or constraints on a table. SQL Server gives constraint-creation privilege to the table owner, the 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 DBPROP_INDEX_AUTOUPDATE.
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.

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 DBPROP_INDEX_INITIALIZE.
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 DBPROP_INDEX_NULLCOLLATION.
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 DBPROP_INDEX_NULLS.
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.

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 DBPROP_INDEX_SORTBOOKMARKS.
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 DBPROP_INDEX_TEMPINDEX.
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 DBPROP_INDEX_TYPE.
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.

Example

This example creates a primary key index.

////////////////////////////////////////////////////////////////////////
// Create the referential integrity, PRIMARY KEY constraint on
// OrderDetails as follows:
//
// 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);
  }