Remote Query

SQL Server generates a SQL query that evaluates a portion of the original query that can be evaluated in its entirety by the provider. This scenario is possible only against SQL Command providers. For the subset of the SQL grammar that is used by SQL Server to generate remote queries, see “Transact-SQL Subset Used for Generating Remote Queries” later in this chapter.

When SQL Server generates the SQL text to be executed remotely, the table and column names are quoted with the quoting character of the provider as reported through the DBLITERAL_QUOTE literal of the IDBInfo interface. If this literal is not supported, table and column names are not quoted.

If the provider supports parameterized query execution, SQL Server considers a parameterized query execution strategy to evaluate a join of a remote table with a local table. The parameterized query is executed repeatedly for parameter values generated from each row of the local table. This strategy reduces the number of rows that are retrieved from the provider and is beneficial when a local table with a small number of rows is joined with a remote table with a large number of rows. This remote join strategy can be enforced by the REMOTE join optimizer hint. For more information about parameterized query execution, see SQL Server Books Online.

The following are the higher-level steps against the provider in the remote query scenario:

• SQL Server creates a Command object from the Session object by using IDBCreateCommand::CreateCommand.
• If the Remote Query Timeout server configuration option is set to a value > 0, SQL Server sets the DBPROP_COMMANDTIMEOUT property on the Command object to the same value by using ICommandProperties::SetProperties; ICommand::SetCommandText must be called to set the command text to the generated Transact-SQL string.
• SQL Server calls ICommandPrepare::Prepare to prepare the command. If the provider does not support this interface, SQL Server continues with Step 4.
• If the generated query is parameterized, SQL Server uses ICommandWithParameters::SetParameterInfo to describe the parameters and IAccessor::CreateAccessor to create accessors for the parameters.
• SQL Server calls ICommand::Execute to execute the command and create the rowset.
• SQL Server uses the IRowset interface to navigate and consume rows from the table. Use IRowset::GetNextRows to fetch rows, IRowset::RestartPosition to reposition to the beginning of the rowset, and IRowset::ReleaseRows to release rows.

Provider Properties of Interest for Remote Query Execution

The Transact-SQL grammar supported by the provider determines the extent to which SQL Server can delegate query execution.

In many cases, SQL Server uses nested SELECT statements in the FROM clause of a query when it generates the query strings to be executed remotely. Because nested SELECT support is not required by SQL-92 entry level, SQL Server does not delegate queries with nested SELECT statements to the provider by default. However, the provider can support a SQL Server specific property called SQLPROP_NESTEDQUERIES through IDBProperties, which enables SQL Server to take advantage of nested queries. Alternatively, the administrator can also set the Nested Queries provider option on a particular provider to make SQL Server generate nested queries against the provider.

SQL Server uses parameterized query execution with a question mark (?) as the parameter marker in the Transact-SQL string. Parameterized query execution is used against the SQL Server, Jet, and Oracle OLE DB providers. Against other providers, parameterized query execution is used if the provider supports ICommandWithParameters on the Command object and at least one of the following conditions are met:

• The provider indicates the ODBC Core level of SQL Server support through the DBPROP_SQLSUPPORT property.
• The provider indicates support for the question mark (?) parameter marker by supporting the SQLPROP_DYNAMICSQL SQL Server-specific property through IDBProperties. For more information, see “SQL Server-Specific Properties” later in this chapter.
• The administrator sets the Dynamic Parameters provider option on the provider to make SQL Server generate parameterized queries.

Character Set and Sort Order Implications

SQL Server has a server-wide, character-set code page for non-Unicode (based on ANSI code pages) string data. There are also server-wide sort orders defined for both Unicode and non-Unicode character data. The sort order used to evaluate string comparisons and sorting in a distributed query is always the local sort order of the server. Similarly, non-Unicode string data from a remote table is interpreted in the code page defined in the local SQL Server.

SQL Server delegates string comparisons to the provider only if the character set (for non-Unicode data), sort order, and string comparison semantics used by the linked server are the same as those used by the local server.

In the case of SQL Server linked servers, SQL Server automatically determines character set and sort order compatibility. For other providers, the administrator must indicate to SQL Server whether the linked server has the same character set, sort order, and string comparison semantics as the local SQL Server. If the semantics are the same, the Collation Compatible linked server option can be set to true by using the sp_serveroption stored procedure. This option should be set to true only if both of the following conditions are met:

• The remote sort order and character set are the same as the local SQL Server.
• The string comparison semantics used by the OLE DB provider follow that of SQL-92 standard specifications or equivalently the comparison semantics of SQL Server.