SQL Syntax Recommendations

The Microsoft® SQL Server™ 2000 Transact-SQL version complies with the Entry level of the SQL-92 standard, and supports many additional features from the Intermediate and Full levels of the standard.

The OLE DB and ODBC application programming interfaces (APIs) were developed with the understanding that applications would use:

• SQL-92 syntax when it provides the functionality needed by the application. Because the SQL dialects of most databases now comply with the Entry level of SQL-92 and support many features in the Intermediate and Full levels, this means many OLE DB providers and ODBC drivers can simply pass through most SQL-92 syntax without having to transform it to something accepted by the database.
  
  
• Use the ODBC extensions to SQL-92 when they provide functionality needed by the application that SQL-92 does not support.
  
  
• Use the native SQL syntax of the database engine when it provides functionality needed by the application that SQL-92 and the ODBC extensions do not support.

This approach minimizes the overhead of OLE DB providers and ODBC drivers. The providers and drivers only have to parse incoming SQL statements for ODBC escape sequences or SQL-92 syntax not accepted by the database. Any ODBC escape sequences and unsupported SQL-92 syntax are transformed into the corresponding SQL syntax accepted by the database engine. All other SQL syntax is passed through to the database engine.

SQL Server 2000 applications using OLE DB, ODBC, or one of the other APIs that encapsulate these two, should follow these guidelines:

• Use SQL-92 syntax when it provides the functionality required by the application.
  
  
• Use ODBC escape sequences when they provide functionality needed by the application but not provided by SQL-92.
  
  
• Use Transact-SQL syntax when it provides functionality required by the application but not provided by SQL-92 or the ODBC escape sequences.

Using SQL with DB-Library and Embedded SQL

DB-Library supports only Transact-SQL. DB-Library does not support the ODBC escape sequences or XML functionality.

Embedded SQL for C supports only the SQL syntax defined in Embedded SQL for C and Microsoft® SQL Server™.

DB-Library has not been extended after SQL Server version 6.5. It operates as a 6.5-level client and cannot use some new features introduced in Microsoft SQL Server 2000 and Microsoft SQL Server version 7.0. Embedded SQL uses DB-Library to communicate with SQL Server, so it also has the same restrictions. For more information, see Connecting Early Version Clients to SQL Server 2000.