[摘选] - Relational Engine之Client Architecture

    Client Architecture

    "What is the native interface of SQL Server?". Many developers were resistant to interfaces like ODBC because they thought that DB-Library, the client API developed by Sybase, was the native interface to SQL Server. In fact, the SQL Server Relational Server itself doesn't have a native API; its interface is TDS(Tabular Data Stream), the protocol for the communication stream between the client and server. TDS encapsulates the SQL statements sent from the client to the server, as well as the result sets sent from the server back to the client. Any API that directly processes TDS is a native SQL Server interface.

    There are two interfaces for SQL Server that can be considered native for SQL Server 7.0, OLE-DB and ODBC. ADO maps to OLE-DB and RDO maps to ODBC, they are not strictly part of SQL Server's client architecture.

 

   

    Client architecture

    There are three components to any of these client APIs, as you can see in Figure 2, above. The top piece implements the API specifics, such as what a rowset looks like, what cursors look like, and so forth.

    The TDS formatter takes an actual request, such as a SQL statement, and packages it up as a TDS message, ships it off to the SQL Server, gets back results, and then feeds the results into the interface implementation.

    There is also some common library code used by all the providers. For example, the BCP facility is a library that both ODBC and OLE-DB call. DTC is another example.

 

    TDS  protocol: As already mentioned, a client's primary method of communication with SQL Server is through the use of TDS messages. TDS is a simple protocol. When SQL Server receives a message, it can be thought of as an event occurring. First, a client sends in a login message (or event) on a connection and gets back a success or failure response. When you want to send in a SQL statement, there is a SQL language message that a client can package up and send to SQL Server. Also, when you want to invoke a stored procedure, a system procedure, or a pseudo system stored procedure, the client can send an RPC message, which corresponds to an RPC event on the SQL Server. In these last two cases, the server then sends back results in a token stream of data. Microsoft does not document the actual TDS messages, as this is considered a private contract between SQL Server components.

    RPC message: 使用参数化的SQL、TSQL语句，以RPC message的方式提交给SQL Server. 相比之下，没有使用参数化的SQL语句，称为ad hoc SQL。注意SQL与TSQL的区别，ad hoc SQL应该只是指没有使用参数化的SQL（DML语句）。

    Pseudo system stored procedure: When server-side cursors were first being developed for SQL Server 6.0, the developers had a choice about how they would manage client/server interactions. Cursors did not fit neatly into the available set of TDS messages, because they allowed row after row of data to be returned without the client specifying additional SQL statements. The developers could have added additional messages to the TDS protocol, but then too many other components would need to be changed. The version of TDS in SQL Server 6.0 also needed to stay close to the Sybase version in order to guarantee interoperability, and so the developers came up with alternative mechanism. They made the new (server-side cursor) features appear to be system stored procedures when in reality the stored procedures are just entry points into the SQL Server code base. They are invoked from a client application using standard RPC TDS messages. They are called pseudo system stored procedures, because they are invoked at the client like a stored procedure, but they are not made up of simple SQL statements like other stored procedures. Most of these pseudo system stored procedures are private and undocumented.In the case of the cursor procedures, all the APIs expose their own set of cursor API models and their own cursor manipulation functions, so there is no reason to document the stored procedures themselves. Even in the Transact-SQL language there is syntax that exposes the cursors, using DECLARE, OPEN, FETCH, and so forth, so there was no need at all to document the pseudo system stored procedures, such as sp_cursor, that are only used internally.（Pseudo system stored procedure都是使用服务器上编码的dll实现，作为SQL Server需要提供的一些支持的接口方式，而不是使用TSQL。区别于其它的存储过程，其它存储过程都可以使用sp_helptext存储过程查看这些存储过程的TSQL代码）

    Catalog stored procedures are another key part of the client/server interaction. The ODBC and OLE-DB APIs define standard ways of describing the metadata about database objects. These standards need to be usable against all kinds of RDBMS servers and are not tuned to SQL Server's own system tables. Instead of the client sending multiple queries against the system tables to the server and constructing this standard view of the metadata on the client, a set of system stored procedures was created that sits down on the server and returns the information in the right format for that API. 

    The procedures written for ODBC were documented. This allows Transact-SQL procedures and DB-Library applications to access metadata without writing complex queries against the SQL Server system tables, and isolates the application from changes that Microsoft makes to the system tables in the future.

    OLE DB defines a set of schema rowsets that are analogous to, but different from, ODBC's metadata. A new set of catalog stored procedures was created to efficiently populate these schema rowsets. However, this new set of stored procedures was not documented because the stored procedures duplicate the earlier functionality. With several existing ways to obtain metadata, the SQL Server team decided to avoid exposing one that added no value to the programming model.

    ODBC and OLE DB expose the concept of parameterized queries and the Prepare/Execute model（在Server Architecture中详细说明）. Prior to SQL Server 7.0, these concepts were implemented by code in the client API. In SQL Server 7.0, Microsoft added support to the Relational Server for these concepts and exposed that support through new pseudo system stored procedures. Support for parameterized queries, through the sp_executesql procedure.

 

    All clients that can communicate with SQL Server, then, are built on these three sets of functionality: the TDS protocol, the catalog stored procedures, and the pseudo system stored procedures.

 

    <Microsoft SQL Server Query Processor Internals and Architecture>