April 8, 2024 at 12:00 am
Comments posted to this topic are about the item Join Hint Limitations
April 8, 2024 at 9:04 am
Thank you. You added a sparkle to my day!
As I tried to 'cheat' and learn, I looked up the new-to-me REMOTE-join hint and I read Aaron Bertrand's article [1] on it from 'somedate' (I get a 'little disappointed' when there is no timestamp; it makes the info much less usefull as the time-context is lost). In the comments Paul White warns about the implicit FORCE ORDER that comes with the REMOTE hint.
This is why I could reason that a COLLATE would/could make a 'force order' complicated and might not work.
The docs just give you the fish; Paul White teached me to fish; thank you!
Regards,
[1]: https://www.mssqltips.com/sqlservertip/2765/revisit-your-use-of-the-sql-server-remote-join-hint/
April 8, 2024 at 10:39 am
From BOL:
REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.
The query fails only if you specify the collation in the compare of the join else it runs correctly.
So, the correct answer is "It works as expected"
This works OK:
SELECT p.Name, pr.ProductReviewID
FROM Production.Product AS p
INNER REMOTE JOIN SQL02.Inventory.Production.ProductReview AS pr
ON p.ProductID = pr.ProductID
ORDER BY ProductReviewID DESC
COLLATE Traditional_Spanish_cs_as ASC;
This fails:
SELECT p.Name, pr.ProductReviewID
FROM Production.Product AS p
INNER REMOTE JOIN SQL02.Inventory.Production.ProductReview AS pr
ON p.ProductID = pr.ProductID
and p.ProductReviewID = pr.ProductReviewID COLLATE Traditional_Spanish_cs_as
ORDER BY ProductReviewID DESC
COLLATE Traditional_Spanish_cs_as ASC;
April 9, 2024 at 9:09 am
From BOL:
REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.
The query fails only if you specify the collation in the compare of the join else it runs correctly.
So, the correct answer is "It works as expected"
This works OK:
SELECT p.Name, pr.ProductReviewID FROM Production.Product AS p INNER REMOTE JOIN SQL02.Inventory.Production.ProductReview AS pr ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC COLLATE Traditional_Spanish_cs_as ASC;
This fails:
SELECT p.Name, pr.ProductReviewID FROM Production.Product AS p INNER REMOTE JOIN SQL02.Inventory.Production.ProductReview AS pr ON p.ProductID = pr.ProductID and p.ProductReviewID = pr.ProductReviewID COLLATE Traditional_Spanish_cs_as ORDER BY ProductReviewID DESC COLLATE Traditional_Spanish_cs_as ASC;
Yup! Tested it and you are right! (and I was wrong) :-|. Only when collating on the join predicate it fails to 'enforce' the hint. Which -in hindsight- sounds fair :-).
So answer a) is the correct one... Mr. Jones might change that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply