Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 262

Getting ready

There is no automatic way to find this behavior; this is simply a manual process. You either
keep this step in mind while developing the SQL script or while working on performance
tuning projects.

Let us use two tables from the AdventureWorks2012 database to demonstrate this exercise.

F [AdventureWorks2012].[HumanResources].[Employee]
F [AdventureWorks2012].[HumanResources].[EmployeePayHistory]

How to do it...

Follow the steps given here to perform this recipe:

1. Select some basic details of an employee from the Employee table and get

the latest the rate (considering rate as the hourly rate of an employee) from the
EmployeePayHistory table. Suppose we want to select those employees whose
latest hourly rate multiplied by eight is less than 152. A developer's obvious logic
would be like this:
SELECT
E.LoginID
,E.JobTitle
,E.BirthDate
,E.MaritalStatus
,E.Gender
,E.HireDate
,EP.HourlyRate
,EP.RateChangeDate
FROM [AdventureWorks2012].[HumanResources].[Employee] AS E
JOIN
(
Select
Max(BusinessEntityID) AS BusinessEntityID
,Max(RateChangeDate) AS RateChangeDate
,Rate AS HourlyRate
FROM
[AdventureWorks2012].[HumanResources].[EmployeePayHistory]
GROUP BY
Rate
) as EP
ON E.BusinessEntityID=EP.BusinessEntityID
WHERE EP.HourlyRate*8<=152