- Collation Compatible: False
- Use Remote Collation: False
In all other cases set collation compatibility to True, so indexes on the remote SQL Server tables can be usedleading to a significant performance boost.
I usually set the RPC / RPC Out and Data Access properties to True.
(click to enlarge)
When the linked server is a read-only data store I definitely prefer to use an OPENQUERY construction:
select * from openquery
(
SQLSERVER2,'SELECT col1,col2 FROM CodeCatalog.dbo.recommendations'
)
over the four part name equivalent:
SELECT col1,col2 FROM SQLSERVER2.CodeCatalog.dbo.recommendations
Reason?
Far less distributed transaction errors (none so far!) generated by the OLE-DB driver.
Using OPENQUERY requires a bit more code, but it really, really pays of...
Performance.
Because linked servers are not the fastest objects on the planet, try to access them as less as you can. I'll usually access them just once and dump te result in a temporary table and use that as input for further processing.
sample:
create table #t (id int)
insert into #T (id)
select * from openquery
(
SQLSERVER2,'SELECT id FROM CodeCatalog.dbo.recommendations'
)
...process results from #T here....
Don't forget to script your linked server definitions and store them somewhere safe.
also, check out my post on msdtc.
