Two ways SQL Server queries across servers

Searching the Internet for cross-server queries, presumably linked server (pre-storage connection mode and ensure connectivity) and OpenDataSource (written in the statement, the portability of strong). Depending on the function used, the performance difference is obvious ... Although very simple, but LZ was pit for a few days only to find, ashamed.

1. Linked Server

Very useful function, add the method if there is a problem can contact LZ alone, not very complex ~

Use method One:

1 Select *  from Linkedserver1.table1

But the implementation of this approach is the same as opendatasouce, that is, the other side of the server all the relevant tables of data are downloaded to local execution. In contrast, using OPENQUERY, you can put query execution on the other side of the server execution, and the content of the transfer only query results, can greatly save the transmission of consumption. Especially when you have tables that involve more than one other server!

★ Use Method Two:

Select *  from OpenQuery (LinkedServer1,'select * FROM table1 join table2')

2. OpenDataSource

Similar to the above, there is also a function that can directly return query results, OpenRowSet.

About their use can be found in the following the classmate wrote, very detailed.

Http://www.cnblogs.com/OpenCoder/archive/2010/03/18/1689321.html

Anyway, I hope every sqler need cross-server query can avoid detours. Thank you.

Two ways SQL Server queries across servers