I have a query :
select * from LinkedServer.DB.dbo.Table
where DATE BETWEEN '1/1/2021' AND '1/31/2021'
AND data = 'C1070'
When 'Use Remote Collation' is set to True , the query runs in 3 seconds , when set to False, it runs in 2 minutes...Anyone have any clue as to why it takes so long to do comparison/translation from Latin1_General_BIN to SQL_Latin1_General_CP1_CI_AS
both servers are sql server 2016.
This specific query does not need Remote server collation designation, but other queries do, as source server has binary sort order.