在链接服务器上使用Top()的SQL子查询出现问题 [英] Problem with SQL subquery using Top() on Linked Server

问题描述

我正在使用SQL Server 2008，并且具有以下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

与本地SQL Server实例上的dbo.Customers和dbo.Orders一起使用时，此方法可以按需要工作.对于客户表中返回的前10个客户ID，它会返回订单表中的所有行-1688行.

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.

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

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.

我不是SQL专家，所以我不知道为什么它的行为应该有所不同.

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

任何建议表示赞赏.

推荐答案

在子查询中有一个TOP(10)，并且没有ORDER BY可以引导，这意味着您不能保证每次都获得相同的10行(对于链接服务器来说尤其如此，因为即使排序规则相同，排序规则匹配也可能使用不同的算法.)

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).

在子查询中添加ORDER BY子句，以使该部分保持一致和稳定，其余部分可以正确执行.

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

这篇关于在链接服务器上使用Top()的SQL子查询出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！