Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server – Logical and Physical Operators

Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement.

The query optimizer uses these operators to build an execution plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators. You can check these operators in the execution plan.

Types of Operators are-

1. Logical Operators
2. Physical Operators

Logical Operators

Logical operators describe the relational algebraic operation used to process a statement. In other words, logical operators describe conceptually what operation needs to be performed.

Physical Operators

Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. For example, some physical operators access columns or rows from a table, index or view. Other physical operators perform other operations such as calculations, aggregations, data integrity checks or joins. Physical operators have costs associated with them.

The physical operators initialize, collect data, and close. Specifically, the physical operator can answer the following three method calls

Usually, a logical operation can be implemented by multiple physical operators. However, in rare cases, a physical operator can implement multiple logical operations as well.

Example of Logical and Physical Operator


SELECT TOP 10 ID, FromPoint, ToPoint, Distance
FROM [dbo].[Area]

Check out the execution plan for Logical and Physical Operators

Pawan Khowal - Execution Plan - Logical and Physical Operators
Pawan Khowal – Execution Plan – Logical and Physical Operators

Right click on each operator and click properties to check out the logical and physical operations.

Related Post

https://msbiskills.com/2015/06/30/what-are-the-internal-methods-of-physical-operators-how-they-work

In the next post i will explain how a query executes in SQL Server, What are the internal steps used by Query Optimizer. Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com

Advertisements