Logical Query Processing Phase in SQL Server

Logical Query Processing Phase in SQL Server

A couple of days back, I had experienced some imperative books and articles about how to question functions inside. in this way, I have chosen to share such snippet of data in my article. This article is useful for each engineer. So… how about we start

In addition, SQL Server deals with one of a kind request it’s known as Logical inquiry handling stage. These stages produce virtual tables with each virtual table encouraging into the following stage. We can see virtual tables.

Request of question statements with coherent inquiry preparing step numbers

  1. (5) SELECT (5-2) DISTINCT (7) TOP(<top_specification>) (5-1) <select_list>  
  2. (1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>  
  3. | (1-A) <left_table> <apply_type> APPLY <right_input_table> AS <alias>  
  4. | (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>  
  5. | (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>  
  6. (2) WHERE <where_predicate>  
  7. (3) GROUP BY <group_by_specification>  
  8. (4) HAVING <having_predicate>  
  9. (6) ORDER BY <order_by_list>  
  10. (7) OFFSET <offset_specs> ROWS FETCH NEXT <fetch_specs> ROWS ONLY;  

 

Request for Logical advance succession of a Query:

1. FROM / JOIN/ APPLY/ PIVOT/ UNPIVOT

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT list/ DISTINCT

6. ORDER BY

7. TOP/ OFFSETFETCH

 (1) FROM: – From phase identifies the query’s source tables and Processes table operators as well as generates virtual table VT1. Each table operator applies a series of sub phases.

Examples

Cartesian product: – This phase performs a Cartesian product (cross join) between the two tables involved in the table operator, generating VT1-J1.

ON Filter: – This phase filters the rows from VT1-J1 based on the predicate that appears in the ON clause (<on predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT1-J2.

Add Outer Rows: – If OUTER JOIN is specified (as opposed to CROSS JOIN or INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT1-J2 as outer rows, generating VT1-J3.

(2) WHERE: – This phase filters the rows from VT1 based on the predicate that appears in the WHERE clause (<where predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT2.

(3) GROUP BY: – This phase arranges the rows from VT2 in groups based on the column list specified in the GROUP BY clause, generating VT3. Ultimately, there will be one result row per each group.

(4) HAVING: – This phase filters the groups from VT3 based on the predicate that appears in the HAVING clause (<having predicate>). Only groups for which the predicate evaluates to TRUE are inserted into VT4.

(5) SELECT: – This phase processes the elements in the SELECT clause, generating VT5.

Evaluate Expressions: – This phase evaluates the expressions in the SELECT list, generating VT5-1

DISTINCT: – This phase removes duplicate rows from VT5-1, generating VT5-2.

TOP: – This phase filters the specified top number or percentage of rows from VT5-2 based on the

logical ordering defined by the ORDER BY clause, generating the table VT5-3.

(6) ORDER BY: – This phase sorts the rows from VT5-3 according to the column list specified in the ORDER BY clause, generating the cursor VC6.

Reference books

 

http://www.hughes.ca/bookmarks/sql/70441/Bonus/9780735623132_InsideSQLSrvr05T-SQLQuerying.pdf

Summary

I hope this article will help to understand the Query logical processing. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Leave a Reply

Your email address will not be published. Required fields are marked *