Query two SQL Server databases on two different servers

Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2). Both are SQL Server databases.

Is it possible to query both databases in the same T-SQL command or function? Perhaps by connecting to one, then getting a result set, then connecting to the other, and getting that result set. Then performing a union with both result sets?

I can do this from within .NET or Java code, but I want to do this strictly from the database side. Is this even possible?

You can indeed do this, but I recommend against it. You can create a linked server on DB-SERVER-1, connecting it to DB-SERVER-2.

(A linked server definition is setup by the DBA. It contains the login credentials to the remote server. If you want to provide the login credentials yourself each time, that would be an openquery or openrowset command.)

You then could write a query using four part naming convention as follows, assuming you are running this query on DB-SERVER-1:

select * 
  from DB1.dbo.YourTable a
  join [DB-SERVER-2].DB2.dbo.OtherTable b on b.ID = a.ID

Cross server joins are notorious for having performance problems. I would only do it for administrative purposes, and definitely not against large result sets.

StackExchange Database Administrators Q#24054, answer score: 4