I'm trying to join a few rows from a remote view to a local table. The view has about 300 million rows, so I want to use the REMOTE query hint so that all 3 million rows don't have to be transferred to my computer.
SELECT R.Something, L.ID, L.Something
FROM [dbo].[LocalTable] L
INNER JOIN (
SELECT TOP 100 Something, L_ID FROM [RemoteServer].[RemoteDB].[dbo].[RemoteTable]
) R
ON L.ID = R.L_ID
This returns 100 rows, as I expected, and takes basically no time, as I expected.
However,
SELECT R.Something, L.ID, L.Something
FROM [dbo].[LocalTable] L
INNER REMOTE JOIN (
SELECT TOP 100 Something, L_ID FROM [RemoteServer].[RemoteDB].[dbo].[RemoteTable]
) R
ON L.ID = R.L_ID
starts to return thousands of rows. I quit it after a few seconds, but it was in the tens - hundreds of thousands.
How could a query hint change my result set?
TOP
withoutORDER BY
is meaningless, but it's not much better to grab the top n rows if they're not actually in the set you're interested in - what was the point of grabbing the top 100 rows here? 4)