Programming for SQL Server

There are several methods of programming Microsoft® SQL Server™ applications.

Transact-SQL

Transact-SQL is the SQL Server-enhanced version of the SQL database language. Client applications use Transact-SQL to communicate with SQL Server. Transact-SQL provides statements for creating and manipulating database objects, and for inserting, updating, and selecting data. The Transact-SQL enhancements include data integrity features and stored procedures (compiled static SQL statements). Stored procedures allow much of an application's processing logic to be shifted from the client application to SQL Server. Stored procedures can contain most Transact-SQL statements, including Transact-SQL control-of-flow statements. Furthermore, stored procedures are precompiled, so the statements don't have to be parsed each time the procedure is executed.

DB-Library for C Applications

DB-Library for C enables the database to become an integral part of an application. Transact-SQL statements can be incorporated into the application, allowing the application to retrieve and update values from a database. Through DB-Library for C, values from the database can be placed in program variables for manipulation by the application. Conversely, values in program variables can be inserted into the database.

Although DB-Library for C contains many functions, giving the application much control over its interaction with SQL Server, most applications require only a few functions. The actual process of connecting with SQL Server, sending Transact-SQL statements to SQL Server, and manipulating the resulting data is straightforward.

DB-Library Compared to Embedded SQL

DB-Library differs distinctly from the Embedded SQL for C type of language interface:

• The DB-Library interface does not require a language precompiler.
  
  
• DB-Library consists of C functions that are not preprocessed into an intermediate form.

Avoiding preprocessing can make database applications more straightforward to write and to debug. However, for programmers new to developing SQL Server applications or who are familiar with other database platforms, Embedded SQL offers a simpler programming interface. Tools for building Embedded SQL applications are available separately from Microsoft.

DB-Library for C Compared to Microsoft ODBC

In addition to DB-Library, SQL Server supports the Microsoft Open Database Connectivity (ODBC) call-level API. ODBC enables applications for Microsoft Windows NT® 4.0, Microsoft Windows®, Windows 95, and Windows 98 to access multiple data sources, including a wide range of relational databases and local indexed sequential access method (ISAM) data. In most cases, DB-Library and ODBC are equally capable of creating SQL Server client applications. ODBC enables applications to access more data sources. DB-Library provides equivalent functionality in multiple client environments, including Microsoft MS-DOS®. In addition, DB-Library provides specialized support for SQL Server, such as bulk copy, two-phase commit, and text operations.

Tools for developing ODBC applications are available in the Microsoft ODBC Software Development Kit, which is part of the Microsoft Developer Network Level II. The ODBC driver for SQL Server is included with SQL Server.