在链接服务器上使用Top（）的SQL子查询问题(Problem with SQL subquery using Top() on Linked Server)

在链接服务器上使用Top（）的SQL子查询问题(Problem with SQL subquery using Top() on Linked Server)

我正在使用SQL Server 2008，我有以下SQL脚本：

Select o.CustomerId as CustomerNoId, OrderValue, OrderDate From dbo.Orders as o Inner Join ( Select Top (10) CustomerId From dbo.Customers where Age < 60 ) As c On c.CustomerId = o.CustomerId

当与本地SQL Server实例上的dbo.Customers和dbo.Orders一起使用时，它可以按需运行。 它返回从Customers表返回的前10个customerIds的订单表中的所有行--1688行。

但是，我有一个链接服务器，其中包含包含更多行的Customers和Orders表。 当我修改脚本以使用链接服务器中的dbo.Orders和dbo.Customers表时，我得到一个奇怪的结果 - 看起来返回了正确的数据，但只返回它的前10行。

我不是SQL专家，所以我无法弄清楚为什么它应该有不同的行为。

任何建议赞赏。

I am using SQL Server 2008 and I have the following SQL script:

Select o.CustomerId as CustomerNoId, OrderValue, OrderDate From dbo.Orders as o Inner Join ( Select Top (10) CustomerId From dbo.Customers where Age < 60 ) As c On c.CustomerId = o.CustomerId

This works as desired when used with dbo.Customers and dbo.Orders on the local SQL Server instance. It returns all rows from the orders table for the first 10 customerIds returned from the the Customers table - 1688 rows.

However I have a linked server holding the Customers and Orders tables containing many more rows. When I modify the script to use dbo.Orders and dbo.Customers tables from the Linked Server I get a strange result - It appears the correct data is returned, but only the top 10 rows of it.

I am no SQL expert so I can't figure out why it should behave any differently.

Any suggestions appreciated.

最满意答案

在您的子查询中有一个TOP（10）并且没有ORDER BY来启动，这意味着您不能保证每次都获得相同的10行（由于可能使用的算法不同，链接服务器尤其如此）用于校对匹配，即使校对是相同的）。

将ORDER BY子句添加到子查询中，以便您可以使该部分保持一致且稳定，其余部分可以正确遵循。

Well there is a TOP (10) in your Subquery and no ORDER BY to boot, which means that you are not guaranteed to get the same 10 rows every time (this is especially true with linked servers because of the different algorithms that may be used for collation matching, even if the collations are the same).

Add an ORDER BY clause to the subquery so that you can make that part consistent and stable and the rest may follow correctly.