The general-purpose application programming interfaces (APIs) recommended for use in new applications that use Microsoft® SQL Server™ 2000 are:
Several factors should be considered when you select a general-purpose API 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 their 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 such as 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.
Analysis 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 factors presented in the preceding list.
API |
Maturity |
Overhead |
Degree of developer control |
SQL Server 2000 feature support | SQL Server 2000 XML Support | OLAP Services feature support |
Language support |
|---|---|---|---|---|---|---|---|
| ADO | Existing | Object Model over OLE DB | Moderate | Most | Yes | Yes | Microsoft Visual Basic® Microsoft Visual C++® Microsoft Visual J++® |
| URL | Emerging | Streams over OLE DB | Low | Limited | Yes | No | HTML
Active Server Pages (ASP) |
| OLE DB | Existing | Native | High | All | Yes | Yes | Visual C++ |
| ODBC | Existing | Native | High | All | No | No | Visual C++ |
| RDO | Existing | Object Model over ODBC | Moderate | Most | No | No | Visual Basic Visual J++ |
| DAO | Legacy | Object Model over ODBC | Low | Limited | No | No | Visual Basic Visual C++ |
| ESQL | Legacy | Hosted over DB-Library | Low | Limited | No | No | Visual C++ COBOL |
| DB-Library for C | Legacy | Native | High | Limited | No | No | Visual C++ |
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 language API for writing SQL Server extended stored procedures.
An Automation API for evaluating strings that contain user questions against the information in SQL Server databases or OLAP cubes. The English Query server returns the SQL statement or MDX query that will retrieve the answer to the user question.
Analysis Services exposes multiple APIs: Decision Support Objects to manage OLAP and data mining objects, ADO Multidimensional (ADO MD) and OLE DB OLAP extensions for accessing OLAP cubes, and data mining functionality.
An Automation API that gives applications and programming tools access to a model of the meta data in SQL Server databases.