• Article

SQL Dialect Requirements for OLE DB Providers

The level of SQL supported by an OLE DB provider determines how effectively SQL Server 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 whole 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 levels of SQL support that the different versions of SQL Server require in distributed queries are:

  • SQL Server 2005: DBPROPVAL_SQL_SUBMINIMUM

  • SQL Server 2000: DBPROPVAL_SQL_SUBMINIMUM

  • SQL Server 7.0: DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE

Besides supporting a lower level of SQL syntax from underlying OLE DB providers, SQL Server 2000 and later defines a 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.