Choosing an API

The general-purpose application programming interfaces (APIs) recommended for use in new applications that use Microsoft® SQL Server™ 2000 are:

• Microsoft ActiveX® Data Objects (ADO) for most database applications. ADO supports rapid development of robust applications and has access to most SQL Server features. The SQL Server features needed by most applications are supported by ADO when using the Microsoft OLE DB Provider for SQL Server.
  
  
• URLs in Internet applications such as HTML or ASP pages.
  
  
• OLE DB for COM-based tools and utilities, or COM-based system-level development requiring either top performance or access to SQL Server features not exposed through ADO. The OLE DB Provider for SQL Server uses provider-specific properties, interfaces, and methods to expose SQL Server features not covered by the OLE DB specification. Most of these provider-specific features are not exposed through ADO.
  
  
• ODBC for the same class of applications as are listed above for OLE DB, but which are not based on COM.

Selecting a General-Purpose API

Several factors should be considered when you select a general-purpose API to use in a SQL Server application:

• Maturity of the API specification.
  • Existing

    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

    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

    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.

• Overhead.
  • Native APIs

    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

    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

    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.

• Degree of developer control.

  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.

• Access to SQL Server features.

  Some APIs have limitations on the numbers or types of SQL Server features they can use.

• Access to Microsoft SQL Server 2000 Analysis Services features.

  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.

• Programming language and tool support for the API.

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++

Additional APIs

SQL Server also supports a number of interfaces that allow applications to make full use of all SQL Server features:

• SQL Distributed Management Objects (SQL-DMO API)

  A set of COM interfaces for managing and administering SQL Server.

• Replication components (Replication Component Programming API)

  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.

• Data Transformation Services (Data Transformation Services API)

  A set of COM interfaces (based on OLE DB) for defining and executing complex data transformations between OLE DB data providers.

• Extended Stored Procedure API (Extended Stored Procedure API)

  A C language API for writing SQL Server extended stored procedures.

• English Query API (SQL Server and English Query)

  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 APIs (Programming Analysis Services Applications)

  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.

• Meta Data Services (Programming Meta Data Services Applications)

  An Automation API that gives applications and programming tools access to a model of the meta data in SQL Server databases.

See Also

Building SQL Server Applications Overview