The information in this article applies to:
SUMMARYWhen you perform a SQL Server 7.0 distributed query with numeric predicates against a data source other than SQL Server, some numeric predicates may not be sent to the remote server for evaluation. This article discusses how to optimize distributed query execution with examples of an Oracle query. MORE INFORMATION
Microsoft SQL Server version 7.0 provides the ability to perform queries
against OLE DB providers. This is done by using the OpenQuery or OpenRowset
Transact-SQL functions or by using a query with four-part names including a
linked-server name. For example,
SQL Server delegates as much of the query execution as possible to an OLE DB provider that supports SQL. There are, however, some operations that SQL Server does not delegate to the provider, because such operations do not have standard SQL syntax and semantics. One such operation is type conversion. This article describes how to determine why a query is not being delegated to the provider and provides techniques to rewrite the query so that it is delegated. If the query involves arithmetic or Boolean expressions, where the two operands of a comparison or arithmetic operation are of different type, an implicit conversion is done to one of the types before the expression is evaluated. SQL Server does not delegate such queries to providers other than SQL Server. The user can rewrite the query in such a way that data type conversion is not necessary, thereby allowing SQL Server to delegate more of the query to the provider. ExamplesIn Oracle, all fixed-point numeric data (integer, numeric, decimal) are represented as one internal type number(p,s) where p and s are the precision and scale respectively. Integers are represented as numeric with a scale of 0. Therefore, any comparisons of such columns against integer constants require a type conversion. For example for the query
the predicate QTY > 100 is not sent to Oracle because of the type conversion needed from 100 to QTY because they are of distinct types. This query can be rewritten in one of two ways to make SQL Server delegate the predicate to Oracle:
For more information about setting up and using Distributed Queries, search on sp_addlinkedserver, OpenQuery, OpenRowset, and related topics in SQL Server 7.0 Books Online. For more information about Oracle data types, please refer to the section 'Data Types in Oracle and SQL Server' in the SQL Server 7.0 Books Online, as well as the Oracle documentation. Additional query words: prodsql
Keywords : |
Last Reviewed: April 19, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |