4

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
TonE
  • 2,975
  • 5
  • 30
  • 50
  • So the customers table has 1688 rows in your dev environment, how many orders are there? – Bryan S. Sep 10 '09 at 14:23
  • Just a comment because I don't really expect this to be the problem, but is it possible the OrderIDs on the linked database don't match up? – Joel Coehoorn Sep 10 '09 at 14:24
  • what are the versions and Editions of SQL Server that you are executing on and that you are linking to? – RBarryYoung Sep 10 '09 at 14:39
  • @Bryan S - I want the query to return all orders for the Top(10) cutomers. In the dev environment there are 1688 orders belonging to the Top(10) customers, and 50 rows in the Customers table and 20213 in the Orders table. – TonE Sep 10 '09 at 14:41
  • @RBarryYoung - Sql Version is the same on both: 10.0.1600.22. The dev machine is on Service Pack 1. The Linked-Server is Service Pack 2... – TonE Sep 10 '09 at 14:49
  • The inline view (select top (10) ...) is non-deterministic because there is no order by. are the customers being selected on the linked server different? – Shannon Severance Sep 10 '09 at 15:01
  • @Shannon Severance -I did notied the lack of Order By clause (I originally added the Top(10) clause to make testing a bit more managable). However if I remove the Top(10) the results include the same 10 customers joined with many orders. It appears that the Top(10) is being applied to the outer query, rather than (or as well as) the sub-query... – TonE Sep 10 '09 at 15:12
  • @Shannon Severance - I added an Order By clause after you and RBarryYoung's suggestion and it works. Thanks! – TonE Sep 10 '09 at 15:21

2 Answers2

4

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • 1
    I added an Order By CustomerID and it's now working. Not sure why it works on the local server but not the linked-server, but I guess the moral is always use an Order By clause in conjunction with Top(). Many thanks for the help. – TonE Sep 10 '09 at 15:20
  • The reason is in RBarry's answer: "which means that you are not guaranteed to get the same 10 rows every time" – MatBailie Sep 10 '09 at 15:28
  • Right, 10 different rows, means that you get completely different matches (or non-matches) on Orders, thus a completely different row count. – RBarryYoung Sep 10 '09 at 15:45
  • Yep, I understand that the results returned from Top() without Group By are arbitrary. Just confused me that it worked fine on the local server. That's non-determinism I guess ;-) – TonE Sep 10 '09 at 15:49
0

Firstly, your lack of an ORDER BY clause makes your sub-query non-deterministic, as @RBarryYoung pointed out.

Secondly, I would firstly try altering the join order (the sub-query becomes first table_source object for the FROM clause), and if not, try playing with the join hint REMOTE.

Rabid
  • 2,984
  • 2
  • 25
  • 25