Programming the SQLPROPSET_OPTHINTS Property Set

Accessing and Changing Relational Data

Accessing and Changing Relational Data

Programming the SQLPROPSET_OPTHINTS Property Set

Individual OLE DB providers can support some SQL functionality beyond that defined in DBPROPVAL_SQL_SUBMINIMUM, but not all of the functionality in DBPROPVAL_SQL_ODBC_CORE or DBPROPVAL_SQL_ANSI92_ENTRY. The Microsoft® SQL Server™ 2000 query optimizer can use some of the functionality supported by these drivers to increase the performance of distributed queries. These providers can use the SQLPROPSET_OPTHINTS property set to inform SQL Server of the features they support that can speed distributed queries.

Although the SQLPROPSET_OPTHINTS property set is defined in the SQL Server documentation, individual OLE DB provider developers must code support for the property set in their providers. After support for this property set is coded into the provider, SQL Server uses it to optimize the performance of distributed queries.

OLE DB providers that support DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE do not need any of the SQLPROPSET_OPTHINTS properties, except for SQLPROP_DATELITERALS. These providers must support all of the functionality covered by the SQLPROPSET_OPTHINTS property set (except for SQLPROP_DATELITERALS) to qualify for DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE support.

These are the properties reported through SQLPROPSET_OPTHINTS.

Property Description
SQLPROP_ANSILIKE Specifies the LIKE clause is supported as defined in the SQL-92 Entry Level, with the % and _ wildcards.
SQLPROP_DATELITERALS Specifies the provider supports datetime literals, or constants as per Transact-SQL syntax.
SQLPROP_DYNAMICSQL Specifies the provider supports the ODBC parameter marker syntax using question marks: ?.
SQLPROP_INNERJOIN Specifies the provider supports references to multiple tables in the WHERE clause, as long as they are not outer join references.
SQLPROP_GROUPBY Specifies the provider supports the GROUP BY and HAVING clauses in a SELECT statement. The property also specifies the provider supports the AVG, COUNT, MIN, MAX, and SUM aggregate functions, as long as DISTINCT is not specified as an aggregate argument.
SQLPROP_NESTEDQUERIES Specifies the provider supports nested SELECT statements in the FROM clause.
SQLPROP_SUBQUERIES Specifies the provider supports subqueries as defined in the SQL-92 Entry Level.

These are the constants used to define the SQLPROPSET_OPTHINTS property set in the code of OLE DB providers:


{ 0x2344480c, 0x33a7, 0x11d1,

    { 0x9b, 0x1a, 0x0, 0x60, 0x8, 0x26, 0x8b, 0x9e }