SQL Server Linked Server Example Query

by

The format should probably be:

<server>.<database>.<schema>.<table>

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.

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.

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.

See http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/ for some more info.

More Related Contents:

  1. Why do I get the error “Xml data type is not supported in distributed queries” when querying a linked server for non-xml data?
  2. How to find a text inside SQL Server procedures / triggers?
  3. Is there an “IF EXISTS” test for a linked server?
  4. Local and global temporary tables in SQL Server
  5. Get first day of week in SQL Server
  6. What is the significance of 1/1/1753 in SQL Server?
  7. SELECT DISTINCT on one column
  8. Confused about UPDLOCK, HOLDLOCK
  9. SQL Server split CSV into multiple rows
  10. Change Notification with Sql Server 2008
  11. How do you create a yes/no boolean field in SQL server?
  12. Connecting to MS SQL Server with Windows Authentication using Python?
  13. Using COALESCE function to make values separated with commas
  14. TSQL Email Validation (without regex)
  15. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  16. How to find the port for MS SQL Server 2008?
  17. Assign result of dynamic sql to variable
  18. How do I create a multiple-table check constraint?
  19. Stored Procedure and Permissions – Is EXECUTE enough?
  20. Stored procedure – return identity as output parameter or scalar
  21. SQL Server Group by Count of DateTime Per Hour?
  22. Temporarily disable all foreign key constraints
  23. How to import a DBF file in SQL Server
  24. SQL Server Dependencies
  25. SQL Server: Filter output of sp_who2
  26. How can I insert binary file data into a binary SQL field using a simple insert statement?
  27. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  28. Date range overlapping check constraint
  29. How to choose the clustered index in SQL Server?
  30. How can I show the table structure in SQL Server query?

Leave a Comment