SQL Dialect Requirements for OLE DB Providers

The level of SQL supported by an OLE DB provider determines how effectively Microsoft® SQL Server™ 2000 delegates distributed query operations to the OLE DB provider. If a provider does not support SQL, but opens only rowsets, SQL Server must retrieve the entire rowset and perform all logical operations, even if the distributed query only needs a subset of the rows in the source rowset. If an OLE DB provider supports many SQL syntax elements, SQL Server generates more sophisticated queries that let the source provider filter unnecessary rows before returning the rowset to SQL Server.

The OLE DB specification defines a DBPROP_SQLSUPPORT property through which providers can report the level of SQL syntax they support. The minimum level of SQL support that SQL Server versions require in distributed queries are:

• SQL Server 2000: DBPROPVAL_SQL_SUBMINIMUM
  
  
• SQL Server 7.0: DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE

In addition to supporting a lower level of SQL syntax from underlying OLE DB providers, SQL Server 2000 defines a new SQLPROPSET_OPTHINTS property set that providers can use to specify that they support individual SQL syntax elements that are beyond those defined for DBPROPVAL_SQL_SUBMINIMUM. If a provider supports one or two features that can be used to optimize distributed queries, but does not support the full DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE syntax, the provider can use the SQLPROPSET_OPTHINTS properties to notify SQL Server of the optimization features it does support.