Multiple Linked Servers in one select statement with one where clause, possible?

Question

Got a tricky one today (Might even just be me):

I have 8 Linked SQL 2012 servers configured to my main SQL server and I need to create table views so that I can filter all these combined table results only using one where clause, currently I use UNION because they all have the same table structures.

Currently my solution looks as follows:

SELECT * FROM [LinkedServer_1].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_2].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_3].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_4].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_5].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_6].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_7].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_8].[dbo].[Table] where value = 'xxx'

As you can see this is becoming quite ugly because I have a select statement and where clause for each linked server and would like to know if there was a simpler way of doing this!

Appreciate the feedback.

Brakkie101

Confident in saying "No"; if you have multiple tables and that need to all be returned in the same dataset, `UNION` is your "friend". I can't say, however, that that query is going to be particularly quick. I might also recommend using `UNION ALL` rather than just `UNION`, unless there are going to be duplicates (which you want removed). This saves the server having to sort all the data after it's all been compiled and the check for duplicates which don't exist. Something that is going to be very expensive to do, if you have a large dataset. — Thom A, Mar 15 '18 at 14:26
I know the question was asked a while ago, but i had the same problem yesterday. I ended up with a dynamic query. Take a look at this question answers: https://stackoverflow.com/questions/14945367/how-to-query-against-multiple-linked-servers — denfri.dev, Oct 14 '20 at 06:02

score 2 · Accepted Answer · answered Mar 15 '18 at 14:28

Instead of using views, you can use inline table-valued functions (a view with parameters). It will not save initial efforts for creating the queries, but could save some work in the future:

CREATE FUNCTION [dbo].[fn_LinkedSever] (@value NVARCHAR(128))
AS
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM [LinkedServer_1].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_2].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_3].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_4].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_5].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_6].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_7].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_8].[dbo].[Table] where value = @value
);

Also, if possible, use UNION ALL instead of UNION.

I would say `UNION ALL` and `UNION` should not be used like "when possible". They have certain purposes. `UNION` does DISTINCT sort in addition. If we do not need to eliminate duplicate rows, then MSSQL should not do overhead. — hastrb, Mar 15 '18 at 14:35
@hastrb That's what I mean :-) if the business logic allows to use `UNION ALL` — gotqn, Mar 15 '18 at 14:36

Multiple Linked Servers in one select statement with one where clause, possible?

1 Answers1