0

Does joining two tables on different servers need a linked server, as mentioned here? The DBA group controls these server objects, and they don't want to create an excessive number of linked servers.

Is it good to "join" two tables on different servers by first reading one into memory as a variable, and potentially running another query on another server with the variable from the first?

Using C# to access SQL server SERVER_A, and then getting all records from table TABLE_A, we save it to an collection variable, say VARIABLE_A.
Then we could connect to SQL server SERVER_B, run a select query on TABLE_B with this variable VARIABLE_A and try to get the join result of TABLE_A and TABLE_B.

Will this solution achieve the desired results (that is function, and not require additional linked servers)?

Community
  • 1
  • 1
Herbert Yu
  • 578
  • 6
  • 16
  • you can use [linked servers](http://technet.microsoft.com/en-us/library/ms188279.aspx) to join across tables in different dbs on different servers no need for temp variable. – Preet Sangha Jan 15 '14 at 01:24
  • 1
    DBAs are there to support the programming effort. While they may have good reason to throw up some roadblocks when it comes to creating a linked server connection, their ultimate answer must be for the good of the company/project. Get your thoughts together and present your arguments clearly to them. – Sam Axe Jan 15 '14 at 01:26
  • And no, in-memory joins are a *terrible* idea. – Sam Axe Jan 15 '14 at 01:27
  • The short answer is "You cannot run a select query on `TABLE_B` using `VARIABLE_A`". How many rows do you need to pull down from `TABLE_B` on average? – Wagner DosAnjos Jan 15 '14 at 01:32
  • Size of TABLE_A and TABLE_B are ~100K records. – Herbert Yu Jan 15 '14 at 01:38
  • You most definitely need linked servers based on the amount of data. – Wagner DosAnjos Jan 15 '14 at 04:53

1 Answers1

2

In-memory joins (from the application) are a terrible idea. It would require (at best) reading in the entire table, or (at worst) doing a read from the second server for each row returned from the first.

Both of these methods increase the memory overhead, application complexity, and network usage.

The linked server option would be my first choice.

A far distant second choice would be to use replication and replicate the second server's database to the first server. Then you could do an in-server join between databases.

Sam Axe
  • 33,313
  • 9
  • 55
  • 89