Order of Execution in SQL Server

In this article we will see the way execution order works in SQL server, mainly when TOP, Union and order by clause are utilized in a query.

Say we have an Employee table

create table Employees

(EmployeeID int identity(1,1) NOT NULL

,EmployeeName varchar(50) NOT NULL default 'N/A'

,Department varchar(50) NOT NULL default 'N/A'

,Salary float NOT NULL default 0)

Lets populate it with some data

insert Employees values('John Doe','Sales', 1000)

insert Employees values('Jay Adam','Legal', 4000)

insert Employees values('Swati Roy','IT', 3000)

insert Employees values('Rick Fisher','IT', 2000)

insert Employees values('Dan Zeener','Sales', 3000)

insert Employees values('Adam Ray','Legal', 3000)

insert Employees values('Lucas Bossing','Sales', 1500)

insert Employees values('Tanya Barnes','Sales', 600)

insert Employees values('Gary Doe','Legal', 6000)

insert Employees values('Tony Johnson','IT', 2500)

insert Employees values('KEITH Hoff','Legal', 5000)

insert Employees values('Julie BURNS','IT', 3500)

insert Employees values('Sara Hines','IT', 6000)

insert Employees values('Ellen COX','IT', 1000)

Now our problem statement is:

"Find the 2 highest paid employees in Legal Department and 2 highest paid employees in IT department"

We think this is very easy and write our query as below, which is a wrong approach.

The Wrong Approach

select top(2) * from Employees where Department ='Legal'

union

select top(2) * from Employees where Department ='IT'

order by salary desc

 

The result of which is below and of course inaccurate.

EmployeeID

EmployeeName

Department

Salary

44

Jay Adam

Legal

4000

45

Swati Roy

IT

3000

48

Adam Ray

Legal

3000

46

Rick Fisher

IT

2000

 

Clearly, on seeing the data the correct answer is

EmployeeID

EmployeeName

Department

Salary

51

Gary Doe

Legal

6000

53

KEITH Hoff

Legal

5000

55

Sara Hines

IT

6000

54

Julie BURNS

IT

3500

 

Reason for inaccuracy:

The results from query are inaccurate because the order of execution is

  1. TOP clause from both queries
  2. Then UNION clause
  3. And last ORDER BY clause

     

    To elaborate, first the top 2 records from legal department are returned then top 2 record from IT department is returned, they both are union and then the results are sorted by order by clause.

     

The Right Approach:

Since, now we know as why our first query gave results that were not intended, we can go and correct our query. One thing we realize is that first the result needs to be sorted and then Union, but since order by clause in both query with union will give a syntax error, there are other ways to solve this problem which are:

  1. Using CTE
  2. Using Analytical function (rank or row_number)

     

Let's first solve this by using CTE

 

;

with e as

(select top(2) * from Employees where Department ='Legal' order by salary desc

 

),b as

(select top(2) * from Employees where Department ='IT'

order by salary desc

)

select * from e

union ALL

select * from b

order by Salary desc

 

And now we have accurate result:

 

EmployeeID

EmployeeName

Department

Salary

51

Gary Doe

Legal

6000

55

Sara Hines

IT

6000

53

KEITH Hoff

Legal

5000

54

Julie BURNS

IT

3500

 

Let's see the same using analytical function row_number:

 

select EmployeeID, EmployeeName, Department, Salary from (

select *,ROW_NUMBER() over(partition by Department order by Salary desc) r from Employees

where Department in('Legal','IT')

) B where B.r in(1,2)

order by Salary desc

EmployeeID

EmployeeName

Department

Salary

51

Gary Doe

Legal

6000

55

Sara Hines

IT

6000

53

KEITH Hoff

Legal

5000

54

Julie BURNS

IT

3500