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>
