This section provides an examination of the interface choices and recommendations for using each interface.
A call-level interface offers a set of function calls or APIs that enable client applications to interact with a server database. Call-level interfaces usually use parameters specified as pointers to data input and output buffers owned by the application. Because of this reliance on pointers, call-level interfaces are almost always used from the C/C++ language. With some mapping code, these interfaces can be called from languages that lack pointer support, such as Visual Basic, but usually developers in these languages are more comfortable and productive using an object interface.
SQL Server offers two call-level interfaces:
At a functional level, ODBC and DB-Library are similar interfaces. They both offer function calls to perform tasks such as opening a connection to SQL Server, executing an SQL statement, and retrieving data and metadata from SQL Server. They also have similar performance characteristics. While many database vendors support ODBC by using a mapping layer on top of their proprietary interface, this is not true for Microsoft SQL Server. For SQL Server, these two APIs are implemented at the same logical layer in the software architecture; both are "native" interfaces for SQL Server. (In fact, all current SQL Server TPC-C benchmarks are published using the ODBC interface, which is strong evidence of the performance of SQL Server's ODBC implementation.) Both APIs offer full access to the same feature sets, with minor exceptions. In particular, ODBC does not impose a generic, lowest-common-denominator approach to building applications. An ODBC application can be written to be compatible with all databases, or tuned for SQL Server implementation, or anywhere in between.
ODBC is the recommended interface and offers the following advantages over DB-Library:
DB-Library uses different API sets for similar functions that are implemented differently, such as retrieving data using a default result set (dbsqlexec, dbresults, dbnextrows) versus a server cursor (dbcursoropen, dbcursorfetch). ODBC implements these similar functions using the same APIs (SQLExecute, SQLExtendedFetch) and a simple statement option to distinguish a default result set from a server cursor. Because of these special-purpose function calls, DB-Library has many more APIs to learn than ODBC (150 versus 50).
For example, SQL Server stored procedures can be executed using an efficient procedure call network format. DB-Library uses a separate set of APIs to send requests in the network format. ODBC uses the same APIs used for sending nonstored procedure requests and looks for the standard ODBC "call" syntax to trigger the use of this efficient network format.
The code and skills used building a SQL Server application on ODBC can be leveraged to build applications for almost any other SQL database. Of course, the code that uses SQL Server features that are not implemented in other ODBC drivers may have to be isolated in a common code base, but this is usually a small portion of the code.
These advantages present a strong case for developing new call-level applications using ODBC. If a company has existing DB-Library applications, there is no need to rewrite them to ODBC unless they are being revised to take advantage of ODBC features. DB-Library applications have excellent performance and will continue to be supported by Microsoft SQL Server for some time. DB-Library, however, will not generally receive feature enhancements in future releases of SQL Server.
Object interfaces offer a model of database programming "objects" (such as connections, SQL statements, and result sets) that can be created by your application and used to send and retrieve data from the database. You can use the objects by calling methods defined for the object (such as Execute) and by setting or getting properties on the object (such as CursorType).
Object interfaces vary widely in their level of abstraction, exposure of database features, and performance characteristics. They are also usually restricted to specific programming languages. Microsoft offers several object interfaces with overlapping functionality, including OLE DB, ActiveX Data Objects (ADO), Remote Data Objects (RDO), and Data Access Objects (DAO). Other vendors of database programming tools such as PowerBuilder or SQL Windows offer their own object interfaces as part of their tools.
With so many choices, it is difficult to sort out the features and decide which is best for your application. This paper does not compare Microsoft's development tools and object interfaces with third-party tools and their associated object models. Such a comparison is complex and highly dependent on factors such as programmer skills, application requirements, and vendor support. This paper does, however, present the major object interfaces provided by Microsoft Corporation and give some recommendations based on development language and application requirements.
In 1996, Microsoft released the OLE DB Software Development Kit (SDK), establishing a new Component Object Model (COM) interface to tabular (row and column) data providers. Like other parts of OLE, OLE DB defines an object hierarchy and a set of defined interfaces, each of which has defined methods for manipulating data. An implementation of an interface must support all of the methods defined for the interface, so that an application that uses ("consumes") the interface can rely on those methods being fully supported in that implementation. OLE DB abstracts the concept of tabular data, so that SQL and non-SQL data sources can expose common interfaces for data retrieval and manipulation. This allows a number of different data sources to expose common OLE DB interfaces.
To access SQL data, you can view OLE DB as an object version of the ODBC API: a standard, high performance interface to a wide variety of data sources. In fact, an important implementation of OLE DB that ships with the OLE DB SDK supports ODBC data sources. This implementation, known as the ODBC Provider, makes any ODBC data source accessible to OLE DB consumers. The ODBC Provider provides performance and feature support comparable to using the ODBC call-level interface to these same data sources. The SQL Server ODBC driver has been thoroughly tuned and tested to work with the ODBC Provider. Thus, the ODBC Provider can be an excellent alternative for applications that need an OLE COM interface to general SQL Server data.
OLE DB offers some powerful capabilities, such as notifications, interface sharing, and OLE objects as column types, that make it possible to build data-aware OLE components that are difficult to build using the ODBC interface. So if you are building data-aware OLE components to be used by other applications, OLE DB is likely to be your best interface choice. Likewise, if you have your own data source to expose programmatic access to, you should look at OLE DB. For more details about the capabilities of OLE DB, see the white paper, "OLE DB for the ODBC Programmer."
Unlike most object interfaces to SQL Server, however, OLE DB does not make programming any easier than using a call-level interface. OLE DB uses pointer data types extensively, which makes it directly accessible only from C/C++. Writing an application to OLE DB also requires a great deal of interface creation and release code that is unrelated to the task of working with application data. Fortunately, the OLE DB SDK also includes a higher-level object interface called ActiveX Data Objects (ADO) that addresses these concerns.
ADO offers a higher-level object interface on top of OLE DB that avoids much of the programming work without sacrificing functionality or performance. ADO is a pointerless object interface that supports OLE Automation, which makes it callable from both C/C++ and from scripting languages like Microsoft JScript™ development software or Visual Basic Script. For these reasons, ADO is a more appropriate interface for most business applications, including SQL Server, that need access to OLE DB data sources.
ADO is the recommended and supported data interface for Internet applications written with the Microsoft Visual InterDev™ Web development system. These applications call ADO from Active Server Pages (ASPs), incorporating program logic written in Visual Basic Script or JScript. The OLE DB SDK also includes instructions for how to call ADO from Java or C/C++ programs that are authored outside of Visual InterDev. The ADO and OLE DB components shipped in the OLE DB SDK can be freely redistributed with applications that use them.
Microsoft Visual Basic ships with two object interfaces to data:
RDO is an object interface that is closely tied to ODBC and optimized for accessing server databases. It exposes nearly all the functionality available in the SQL Server ODBC driver and makes this functionality easily accessible to Visual Basic programs. RDO supports binding visual controls, such as list boxes and grids, directly to SQL Server data, greatly reducing the amount of code that must be written to display data on the screen. RDO also makes the ODBC call-level interface "handles" available so that a Visual Basic program can use any features in an ODBC driver that were not incorporated in the RDO object model. Even with these many features, RDO imposes little performance overhead on top of ODBC and has a relatively small memory footprint. For these reasons, RDO makes an excellent choice for SQL Server access from Visual Basic programs.
The DAO interface is closely tied to and optimized for the Jet local database, which ships in Visual Basic and Microsoft Access. It offers similar syntax and many of the same features as RDO for getting directly to SQL Server data. For applications that only access SQL Server data, however, DAO offers less functionality and has a larger memory footprint, so it is not the recommended interface. But DAO offers features that RDO does not, such as efficient access to local Jet data and the ability to join data from multiple data sources in a single query. If your application requires these unique features, DAO is the right choice. Conversely, if your application does not involve local Jet data or heterogeneous queries, stick with RDO.
Microsoft plans to develop ADO into the successor of both RDO and DAO. Over time, ADO will become a superset of most of the unique functionality and supported data sources of both of these interfaces. At that point, ADO will be incorporated into Visual Basic and become the primary object interface to data for Visual Basic programs. All three interfaces have a similar object hierarchy and syntax, so migrating applications in the future will not be difficult. In the meantime, ADO is callable from the Visual Basic development environment, but this is not an officially supported or documented configuration as of this writing.
SQL Distributed Management Objects (SQL-DMO) is a specialized object interface for SQL Server administration tasks. It has object properties and methods to support operations such as creating devices and databases, establishing replication relationships, scheduling housekeeping tasks, and defining alert actions on errors reported by SQL Server. SQL Enterprise Manager is layered on top of SQL-DMO, which means that almost anything you can do in the user interface can also be done programmatically. SQL-DMO supports OLE Automation interfaces, which makes it accessible from interpreted languages such as Visual Basic, as well as C/C++.
SQL-DMO is not intended to be a general-purpose interface for application data. Most applications, however, need to accomplish some database administration tasks as part of their setup and maintenance modules. These portions of your application can be programmed using standard Transact–SQL statements and stored procedures or through calls to SQL-DMO. In most cases the SQL-DMO method is easier to write because the object syntax is more consistent and handles tasks like repeating an operation for each object in a database much more concisely. The scheduled tasks and alerts objects of SQL-DMO can be an effective tool for making your application self-managing through automatic maintenance tasks and predefined responses to error conditions. A self-managing server will keep your users happy.
Embedded SQL is an ANSI-standard programming interface in which SQL statements, delineated by EXEC SQL tags, are incorporated into the source code of an application. The source code is input to a precompiler, which identifies the SQL blocks and replaces them with the appropriate low-level function calls for communicating with the database. An Embedded SQL precompiler for SQL Server is currently available for programs written in C in the form of a toolkit that ships on the MSDN™ Library, professional level subscription. This precompiler technology has also been licensed to Micro Focus, who offers it as a toolkit for Cobol programmers. Both precompilers have passed the NIST tests for ANSI-standard compliance with SQL Server version 6.5.
Embedded SQL offers a familiar programming model for developers of applications for other databases such as Oracle or DB2. For Cobol programmers, Embedded SQL is also the most commonly supported database interface of any kind and is an excellent solution for accessing SQL Server. For applications written in C, however, Embedded SQL is somewhat slower than the call-level interfaces and doesn't allow you to take advantage of specific SQL Server features and performance optimizations. The primary design goal for Embedded SQL for C is to follow the strict ANSI standard for maximum portability of applications. Embedded SQL for C is useful if you are porting an application from another database and have a large code base that would be difficult to adapt to ODBC. For these applications, Embedded SQL for C will perform adequately but may not offer optimal performance and control.
Regardless of which interface you choose, you need to learn how to use it effectively. Any interface can be used in ways that create unnecessary overhead and degrade performance. To understand how to get the best performance out of your chosen interface, first look for any technical documentation available on optimizing client/server performance for that interface. Programmers using ODBC or RDO, for example, should read the white paper, "Using ODBC with Microsoft SQL Server." Much of the advice in this paper, such as when and how to use prepared statements and stored procedures, applies to OLE DB and ADO applications as well.
Use SQL Trace, which ships with SQL Server 6.5, to examine the client/server traffic generated by your application. What you find may surprise you. Many commands being sent to the server may not be generated by your application but instead are sent by the interface you are using. Often these extra commands can be avoided by changing a property or an option in the interface and without affecting the functionality of the application.
For example, the ODBC driver sends a SET TEXTSIZE statement to the server every time a statement handle has a nondefault value for SQL_MAX_LENGTH statement option. If your application does not need to limit the maximum length of returned column data, you can avoid unnecessary overhead by leaving that statement option at its default value. SQL Trace can also point to other opportunities for optimization, such as statements that can be batched or transactions that can be committed sooner. If you can optimize the client/server traffic identified by SQL Trace, you are a long way toward optimizing your overall application.