[SQL] Foreign Key - Join Elimination

Turning 時發現過，假如有 JOIN Table 但是沒有 Select 該 Table 任何欄位，Query Optimizer 會排除該 Table，執行計畫內就不會出現，閱讀 Explore the secrets of SQL Server execution plans 後才發現，原來是因為 Foreign Key，所以 Query Optimizer 才能這樣判斷

FK_SalesOrderDetail_SalesOrderHeader 存在

SELECT 
	SOD.* -- 只 select Sales.SalesOrderDetail Table 欄位
FROM Sales.SalesOrderHeader AS SOH
	JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID

 

FK_SalesOrderDetail_SalesOrderHeader 不存在

-- 刪除 FK
ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader]
GO

SELECT 
	SOD.*
FROM Sales.SalesOrderHeader AS SOH
	JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID

• 參考資料
• Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster
• [SQL SERVER]定義FK改善效能