Compare Execution Plans in SQL Server 2016

Compare Execution Plans.sql

-- Drop Index if exists
DROP INDEX IF EXISTS [idx_Person_Email_Date] ON [Person].[Person]

-- Execute a query with a missing index
SELECT e.BusinessEntityID FROM HumanResources.Employee e
INNER JOIN Person.Person p 
ON p.ModifiedDate = e.ModifiedDate
WHERE  p.EmailPromotion = 1


-- Create a Non Clustered Index to optimize query performance
CREATE NONCLUSTERED INDEX [idx_Person_Email_Date]
ON [Person].[Person] ([EmailPromotion],[ModifiedDate])


-- Execute the select query again to view the difference in Execution Plan
SELECT e.BusinessEntityID FROM HumanResources.Employee e
INNER JOIN Person.Person p 
ON p.ModifiedDate = e.ModifiedDate
WHERE  p.EmailPromotion = 1

-- Generate Execution Plans for both SELECT queries and compare the execution plans in SQL Server 2016.