What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan? – Interview Question of the Week #122

Question: What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan?

Answer: I heard this question after a long time. I used to hear this question in my early career. Here is the answer:

Logical Operators
Logical operators describe the relational operation used to process a statement. They describe conceptually what operation needs to be performed.

Examples: Right Anti Semi Join, Segment Repartition

Physical Operators
Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. The physical operators initialize, collect data, and close.

Examples: Index Scan, Clustered Index Delete

There are few operators which are both – logical and physical operators (example: Switch)

It is quite possible that for a single query the logical operators and physical operators are different.

Here is the example of the query where you can see for Join operator, the logical and physical operators are different.

Here is the query which I have created for the sample database AdventureWorks.

USE AdventureWorks2014
GO
SELECT V.Name, PV.Standardprice
FROM Purchasing.ProductVendor AS PV
INNER JOIN Purchasing.Vendor AS V
ON (PV.BusinessEntityID = V.BusinessEntityID)
GO

Now let us inspect the execution plan of the query:

You can clearly see in the execution plan that for the Join operation there are two different operations. The physical operation is Hash Join and the logical operation is Inner Join.

Reference: Pinal Dave (http://blog.SQLAuthority.com)