The general-purpose APIs recommended for use in new applications using Microsoft® SQL Server™ 7.0 are:
There are several factors to consider when selecting which general-purpose APIs to use in a SQL Server application:
Existing API specifications are mature, stable specifications. Supplementary information about the API is readily available in third-party books and classes. There is an existing pool of programmers familiar with the API.
Emerging API specifications are recent and may be evolving rapidly. Supplementary information about the latest version of the API may be scarce. There are relatively few programmers available who have used the API, although programmers familiar with a similar API can be retrained quickly.
Legacy API specifications are stable but unchanging. They may not support new features, and are likely to be discontinued at a future date. Information about the API is readily available, but the pool of programmers familiar with the API may be shrinking.
Native APIs are low-level APIs implemented with providers or drivers that communicate directly to SQL Server using the Tabular Data Stream (TDS) protocol. They are relatively complex APIs, but offer the best performance because they have the least overhead.
Object model APIs use a relatively simple object model to encapsulate a native API. They are less efficient than native APIs because they must map their objects to the underlying native API, but the performance is acceptable for almost all applications. Applications using an object model API are simple to program and maintain. The object model API may not support all of the features of the underlying native API.
Hosted APIs also encapsulate a native API, but do not use an object model. The efficiency, ease-of-use, and feature-set issues for hosted APIs are similar to those for object model APIs.
APIs vary in their overall feature set. Simple APIs like ADO are easy to learn, program, and maintain, but they do not support all of the capabilities of the more complex APIs such as OLE DB and ODBC. You can take advantage of the ease-of-use advantages of the APIs such as ADO, RDO, and ESQL if they provide the functionality the application needs.
Some APIs have limitations on the numbers or types of SQL Server features they can use.
OLAP Services, ADO MD, and OLE DB for OLAP offer support for online analytical processing. These services can be integrated with ADO and OLE DB applications using the OLE DB Provider for SQL Server.
The following table maps the general-purpose database APIs supported by SQL Server to the issues listed earlier.
API |
Maturity |
Overhead |
Degree of developer control |
SQL Server 7.0 feature support |
OLAP Services feature support | Language support |
|---|---|---|---|---|---|---|
| ADO | Emerging | Object Model over OLE DB | Moderate | Most | Yes | Microsoft Visual Basic® Microsoft Visual C++® Microsoft Visual J++® |
| OLE DB | Emerging | Native | High | All | Yes | Visual C++ |
| ODBC | Existing | Native | High | All | No | Visual C++ |
| RDO | Existing | Object Model over ODBC | Moderate | Most | No | Visual Basic Visual J++ |
| DAO | Legacy | Object Model over ODBC | Low | Limited | No | Visual Basic Visual C++ |
| ESQL | Legacy | Hosted over DB-Library | Low | Limited | No | Visual C++ COBOL |
| DB-Library for C | Legacy | Native | High | Limited | No | Visual C++ |
| DB-Library for Visual Basic | Legacy | Object Model over DB-Library | Low | Limited | No | Visual Basic |
SQL Server also supports a number of interfaces that allow applications to make full use of all SQL Server features:
A set of COM interfaces for managing and administering SQL Server.
A set of COM interfaces for defining and managing replication between SQL Server databases. You can also replicate data from heterogeneous databases to SQL Server.
A set of COM interfaces (based on OLE DB) for defining and executing complex data transformations between OLE DB data providers.
A C API for writing SQL Server extended stored procedures.
Overview of Building SQL Server Applications