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:
Extern const GUID SQLPROPSET_OPTHINTS =
{ 0x2344480c, 0x33a7, 0x11d1,
{ 0x9b, 0x1a, 0x0, 0x60, 0x8, 0x26, 0x8b, 0x9e }
};
enum SQLPROPERTIES
{
SQLPROP_NESTEDQUERIES = 0x4,
SQLPROP_DYNAMICSQL = 0x5,
SQLPROP_GROUPBY = 0x6,
SQLPROP_DATELITERALS = 0x7,
SQLPROP_ANSILIKE = 0x8,
SQLPROP_INNERJOIN = 0x9,
SQLPROP_SUBQUERIES = 0x10,
}