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 the functionality in DBPROPVAL_SQL_ODBC_CORE or DBPROPVAL_SQL_ANSI92_ENTRY. The SQL Server 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 one of the SQLPROPSET_OPTHINTS properties, except for SQLPROP_DATELITERALS. These providers must support all 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.
The following table lists the properties that are reported through SQLPROPSET_OPTHINTS.
Property |
Description |
|---|---|
SQLPROP_ANSILIKE |
Specifies the LIKE clause is supported as defined in the ISO Entry Level, with the % and _ wildcard characters. |
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_SQLLIKE |
Indicates the provider supports the SQL Server LIKE syntax. When SQLPROP_SQLLIKE is on, the optimizer can send a query that contains a SQL Server LIKE predicate to the remote server, if doing this is justified by the query plan. If SQLPROP_SQLLIKE is off, a SQL Server LIKE predicate is always evaluated locally. |
SQLPROP_SUBQUERIES |
Specifies the provider supports subqueries as defined in the ISO Entry Level. |
The following are the constants that are 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,
SQLPROP_SQLLIKE = 0x15
}