July 25, 2022 at 5:40 pm
A hosted ProductDB has changed from 2008 R2 to 2019. It was being restored onto a 2014 instance. To support this I've installed a 2019 instance to host it and created a linked server from 2014 --> 2019. So that the same queries can run from the 2014 instance just by prefixing ProductDB with LinkedServer.ProductDB
Latin1_General_CI_AS - Server Collation 2014 and new 2019 instance
SQL_Latin1_General_CP1_CI_AS - Product DB collation on both instances
The linked Server has "use remote collation=True, Collation Compatible=False"
I get different results from Some queries running on 2014 instance if I use the LinkedServer versus the DB in 2014 instance.
Even if I take the last 2008 supplied DB and restore on both 2014 and 2019 instance. I get different results from Linked server vs DB held in same instance, this is for only 2 queries.
The 2 problematic Queries rely on ordering. They pick the first record, which is determined by ROW_Number() and Partition By.
BUT the order by clause is not granular enough to make it unique, so have Multiple records that could be recordNo=1.
I think this is a bug in the query and I could suggest a fix - by making the ordering unique. This would make it diverge from historical records.
Why with this "bug" has recordNo=1 always been the same record and switching over to a linked server has caused it to be picking a different recordNo=1.
Any ideas..
July 25, 2022 at 6:16 pm
Paul White and/or Itzik Ben-Gan have done write ups on the problem with non-unique sorts. The solution is indeed to make sure you specify ORDER BY values that will insure that you have a unique sort.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply