The OLE DB Provider for Microsoft SQL Server Compact 3.5 reveals the ability to create indexes through IIndexDefinition::CreateIndex.

There are limitations on using indexes in SQL Server Compact 3.5. For more information, see OLE DB Indexes (SQL Server Compact) and Changing Indexes with OLE DB.

Examples

The following example shows how to use the OLE DB provider for SQL Server Compact 3.5 to create a composite index over the LastName and FirstName columns in the Employees table of a database.

//Create an index on two columns.
HRESULT             hr;
DBID                TableName;
DBID                IndexName;
DBPROP              dbprop[1];
DBPROPSET           dbpropset[1];
DBPROP              indexdbprop[2];
DBPROPSET           indexdbpropset[1];
DBINDEXCOLUMNDESC   rgIndexColumnDescs[2];
DBID                dbidColumn1;
DBID                dbidColumn2;
IDBInitialize       *pIDBInitialize     = NULL;        
IDBProperties       *pIDBProperties        = NULL;        
IDBCreateSession    *pIDBCreateSession  = NULL;
IIndexDefinition    *pIIndexDefinition  = NULL;


VariantInit(&dbprop[0].vValue);        
VariantInit(&indexdbprop[0].vValue);

// Create an instance of the OLE DB provider.
hr = CoCreateInstance(    CLSID_SQLSERVERCE, 0, CLSCTX_INPROC_SERVER,
    IID_IDBInitialize, (void**)&pIDBInitialize);
if(FAILED(hr))
{
    goto Exit;
}

// Initialize a property with name of database.
// Open an exsiting database myDatabase.
dbprop[0].dwPropertyID     = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions         = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt         = VT_BSTR;
dbprop[0].vValue.bstrVal = L"\\windows\\MyDB.sdf";
if(NULL == dbprop[0].vValue.bstrVal)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Initialize the property set.
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties     = dbprop;
dbpropset[0].cProperties     = sizeof(dbprop)/sizeof(dbprop[0]);

//Set initialization properties.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, 
    
(void **)&pIDBProperties);
if(FAILED(hr))
{
    goto Exit;
}

// Sets properties in the Data Source and initialization property groups
hr = pIDBProperties->SetProperties(1, dbpropset); 
if(FAILED(hr))
{
    goto Exit;
}

// Initializes a data source object 
hr = pIDBInitialize->Initialize();
if(FAILED(hr))
{
    goto Exit;
}

// Get the IDBCreateSession interface.
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,
    (void**)&pIDBCreateSession);
if (FAILED(hr))
{
    //Send an error-specific message and do error handling.
    goto Exit;
}

// Create a session object. 
hr = pIDBCreateSession->CreateSession(NULL, IID_IIndexDefinition, 
    (IUnknown**) &pIIndexDefinition);

// (This sample assumes that we have information about the Employees table.
// database schema.)
// Prepare the table name DBID as Employees.
TableName.eKind          = DBKIND_NAME;
TableName.uName.pwszName = L"Employees";

// Prepare index name DBID as full_name_index.
IndexName.eKind          = DBKIND_NAME;
IndexName.uName.pwszName = L"full_name_index";

// Set up properties for IIndexDefinition::CreateIndex call. For this 
// index,we enforce uniqueness through the DBPROP_INDEX_NULLS property.
// Index properties must have colid set to DB_NULLID.
indexdbprop[0].dwPropertyID   = DBPROP_INDEX_NULLS;
indexdbprop[0].dwOptions      = DBPROPOPTIONS_REQUIRED;
indexdbprop[0].vValue.vt      = VT_I4;
indexdbprop[0].vValue.lVal    = DBPROPVAL_IN_DISALLOWNULL;
indexdbprop[0].colid          = DB_NULLID; 

// Initialize the property set.
indexdbpropset[0].guidPropertySet = DBPROPSET_INDEX;
indexdbpropset[0].rgProperties    = indexdbprop;
indexdbpropset[0].cProperties     = sizeof(indexdbprop)/sizeof(indexdbprop[0]);
 
// Set up DBINDEXCOLUMNDESC structures to define the columns in the  
// index and the ordering for each column within that index.
rgIndexColumnDescs[0].eIndexColOrder = DBINDEX_COL_ORDER_ASC;
rgIndexColumnDescs[0].pColumnID      = &dbidColumn1;
rgIndexColumnDescs[1].eIndexColOrder = DBINDEX_COL_ORDER_ASC;
rgIndexColumnDescs[1].pColumnID      = &dbidColumn2;

// Specify the column names for the composite index on 
// LastName and FirstName.
dbidColumn1.eKind = DBKIND_NAME;
dbidColumn1.uName.pwszName = L"LastName";
dbidColumn2.eKind = DBKIND_NAME;
dbidColumn2.uName.pwszName = L"FirstName";

// Create a two-column composite index named full_name_index over the 
// LastName and FirstName columns in the Employees table. 
hr = pIIndexDefinition->CreateIndex(&TableName, &IndexName,
    sizeof(rgIndexColumnDescs)/sizeof(rgIndexColumnDescs[0]),
    rgIndexColumnDescs, sizeof(indexdbpropset)/sizeof(indexdbpropset[0]),
    indexdbpropset, NULL);

Exit: 

// When finished, clear the properties arrays and release interfaces.
// Uninitialize the environment.

return;