问题描述
在Management Studio中，我试图在两个链接的服务器之间运行查询/联接.这是使用链接的数据库服务器的正确语法吗?
While in Management Studio, I am trying to run a query/do a join between two linked servers.Is this a correct syntax using linked db servers:
select foo.id
from databaseserver1.db1.table1 foo,
databaseserver2.db1.table1 bar
where foo.name=bar.name
基本上，您是否只是将db服务器名称开头为db.table?
Basically, do you just preface the db server name to the db.table ?
推荐答案
格式应为:
<server>.<database>.<schema>.<table>
例如:DatabaseServer1.db1.dbo.table1
For example:DatabaseServer1.db1.dbo.table1
更新:我知道这是一个老问题，我的答案是正确的；但是，我认为绊脚石的任何人都应该知道一些事情.
Update: I know this is an old question and the answer I have is correct; however, I think any one else stumbling upon this should know a few things.
即，在联接情况下对链接服务器进行查询时，来自链接服务器的 ENTIRE 表将可能下载到按顺序执行查询的服务器进行加入操作.在OP的情况下，来自DB1的table1和来自DB2的table1都将全部传输到执行查询的服务器，大概命名为DB3.
Namely, when querying against a linked server in a join situation the ENTIRE table from the linked server will likely be downloaded to the server the query is executing from in order to do the join operation. In the OP's case, both table1 from DB1 and table1 from DB2 will be transferred in their entirety to the server executing the query, presumably named DB3.
如果您有大表，则该可能会导致执行需要很长时间才能执行的操作.毕竟，现在它受到网络流量速度的限制，这比内存甚至磁盘传输速度要慢几个数量级.
If you have large tables, this may result in an operation that takes a long time to execute. After all it is now constrained by network traffic speeds which is orders of magnitude slower than memory or even disk transfer speeds.
如果可能，请对远程服务器执行单个查询，而无需加入本地表，以将所需的数据提取到临时表中.然后查询一下.
If possible, perform a single query against the remote server, without joining to a local table, to pull the data you need into a temp table. Then query off of that.
如果这不可能，那么您需要查看导致SQL Server必须在本地加载整个表的各种情况.例如，使用GETDATE()甚至某些联接.其他表现杀手包括不给予适当的权利.
If that's not possible then you need to look at the various things that would cause SQL server to have to load the entire table locally. For example using GETDATE() or even certain joins. Others performance killers include not giving appropriate rights.
请参见> http://thomaslarock .com/2013/05/top-3-performance-killers-for-linked-server-queries/了解更多信息.
这篇关于SQL Server链接服务器示例查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！