您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 跨伺服器多表查詢：連結伺服器與分散式查詢兩大方案實戰

編輯：關於SqlServer

方案一：建立連結伺服器

適用於頻繁、固定的跨伺服器查詢，一次設定、長期複用。

建立步驟

-- 1. 建立連結伺服器
EXEC sp_addlinkedserver
    @server = 'SRV_LNK',                    -- 本地別名
    @srvproduct = '',
    @provider = 'SQLOLEDB',                 -- SQL Server OLE DB Provider
    @datasrc = '192.168.10.50';             -- 遠端伺服器 IP/主機名

-- 2. 設定登入對應（SQL 驗證）
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'SRV_LNK',
    @useself = 'FALSE',
    @locallogin = NULL,                     -- 所有本地登入對應同一組遠端帳號
    @rmtuser = 'remote_user',
    @rmtpassword = 'StrongP@ssw0rd';
GO

查詢範例

-- 內聯結：本地 IM_BarLend 與遠端 ME_ListCode
SELECT A.ListCode
FROM SRV_LNK.[BarcodeDB].dbo.ME_ListCode A
INNER JOIN IM_BarLend B ON A.ListCode = B.ListCode;

移除連結伺服器

EXEC sp_dropserver 'SRV_LNK', 'droplogins';

方案二：臨時分散式查詢

適用於臨時、一次性的跨伺服器查詢，無需永久物件，權限控制更細緻。

啟用必要設定

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
GO

查詢範例

用後關閉（安全最佳實務）

EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
GO

效能與安全比較

維度	連結伺服器	OPENDATASOURCE
連線池重用	是（長連線）	否（每次新建）
執行計劃快取	可參數化重用	難以重用（連線字串內嵌）
認證管理	集中於連結伺服器定義	分散於各查詢字串
防火牆/網路	持續開放埠	僅查詢瞬間需連通
適用場景	ETL 排程、報表伺服器、應用程式固定查詢	DBA 臨時稽核、跨環境資料比對、遷移腳本

進階技巧與避坑指南

四段式命名： [LinkedSrv].[RemoteDB].[Schema].[Table]，省略 Schema 會預設 dbo。
推述查詢： 使用 OPENQUERY 將整段 T-SQL 推送到遠端執行，減少網路傳輸：

SELECT * FROM OPENQUERY(SRV_LNK,
    'SELECT ListCode, CreatedDate FROM BarcodeDB.dbo.ME_ListCode WHERE CreatedDate > ''2023-01-01''');

分散式交易： 跨伺服器 INSERT/UPDATE/DELETE 需啟用 MSDTC（元件服務 → 電腦 → 我的電腦 → 分散式交易協調器 → 安全性 → 勾選「網路 DTC 存取」、「允許遠端用戶端/管理」）。
權限最小化： 連結伺服器登入對應僅授予 SELECT、VIEW DEFINITION，嚴禁 sysadmin。
統計資料同步： 遠端資料量大幅變動時，本地查詢優化器可能產生劣質計劃，定期執行 EXEC sp_statistics_100 'SRV_LNK', 'RemoteDB', 'dbo', 'TableName' 更新遠端統計資料快取。