[Linked Servers] Trick to making cross-server queries faster

Learned this years ago, and it's one of those nice tricks to keep in your cap.

So, what collation are you running? Do you know? Do you always use the default? Do you use linked servers and need a little more performance?

If you do, then you're in luck. If you have a linked server between two servers running the same collation, enable "collation compatible" and your queries will run faster.

Why? As I remember, if you don't have it enabled, then your query is sent across without the WHERE clause. Once it comes back, it's evaluated, ensuring that collation is properly dealt with. If you have collation compatible = true, then it sends over the whole query, including the WHERE clause. So, fewer results returned, lower I/O on the far-side, and no processing required locally.

One thing, though - make sure you're on the same collation. On 2005, the default is still the same (IIRC), but what it's called has changed.