The Logical Query Processing Order; T-SQL

 

The Logical Query Processing Order T-SQL


Today we are going over the T-SQL (Transact-SQL) logical query processing order for a SELECT
statement. I've linked everything I used below.



What is the logical query process?
This is the way that standard SQL defines how a query is processed. 

Although this process is the conceptual guideline, SQL sometimes bypasses it during the processing of a query.
 

Note: There are cases where the processing order may differ. See this article for more information.

 

 

 Example Query:

(Using Microsoft's AdventureWorks2014 database, so feel free to try it out in a TEST environment.)


SELECT SalesOrderID AS OrderID, COUNT(OrderQty) AS NumberOfOrders
FROM Sales.SalesOrderDetail
WHERE SalesOrderID <= 43696

GROUP BY SalesOrderID

HAVING COUNT (OrderQty) <= 4
ORDER BY SalesOrderID ASC;
GO

 • I know it starts with SELECT, but you cannot write in a logical processing order without starting with the SELECT clause.

 
"You cannot write the query in correct logical order."
- Itzik Ben-Gan 

• The AS is optional in the syntax but I used it to make the return look simpler.
• The AS command renames the table or column, with an alias that exists for the duration of
the specific query. Using an alias is very helpful when reporting and you want to make the outcome easier to read/ make more sense in regards to the desired return. 

Quick steps to sum up the query (in the logical processing order):

1. A SQL statement that Queries rows FROM SalesOrder ID and OrderQty.
2. Filters only rows WHERE SalesOrderID is equal to or greater than 43696.
3. GROUPs by SalesOrderID.
4. Filters only groups HAVING 4 or more orders.
5. The SELECT returns the result for each group.
6. The ORDER BY sorts the rows in the output by SalesOrderID in ascending order.

 This results in a clustered index seek and the results are in the screenshot below;

 
Results:

 

In conclusion:
The T-SQL is almost like any other language, with syntax being the equivalent of grammar in spoken languages
(you could probably use this exact sentence to describe all other computer languages as well ). Think about this...

 

Thanks for reading!

 
If you like what you’ve read on Techie Chatzz, go ahead and join the mailing list to be notified every time we post techie insight.
Stay tuned techies!

#sqlserver #sqldatabase #funday #funactivities #sql #sqlserver #microsoft #learn #techies #techieshelpingtechies #techiechatzz #technitedwestand #query #mssql #technology #techie4life

 References:
• AdventureWorks sample databases:
AdventureWorks sample databases - SQL Server | Microsoft Docs
• Logical Processing Order of the SELECT statement:
SELECT (Transact-SQL) - SQL Server | Microsoft Docs


Comments

Post a Comment

Popular posts from this blog

Run Queries in SQL Server Using Python!

Run Queries in SQL Server Using Python! By Naomi A. This is a fairly quick one - all of the info needed on how the script works is in the comments! The full script is ready for download on GitHub: (LINK) # Import the pyodbc module. #This module allows Python to connect to an ODBC database, like SQL Server. import pyodbc # The variables hold the server name, database name, username, and password. # This is necessary in order to connect to the SQL Server database. server = 'ServerName' database = 'DBName' username = 'user' password = 'password' # Connection string: # This is a formatted string that the ODBC driver uses to know who is connecting (username and password), # where they are connecting to (server and database), # and what driver they are using to make the connection (ODBC Driver 17 for SQL Server). connection_string = f 'DRIVER= {{ ODBC Driver 17 for SQL Server }} ;SERVER= { server } ;DATABASE= { database } ;UID= { username } ;PWD...
Read more

How to Create a Neural Network for Recognizing Hand-Drawn Shapes

Image
How to Create a Neural Network for Recognizing Hand-Drawn Shapes by Naomi A. In this guide, we will walk through the process of developing a neural network that identifies hand-drawn shapes. The tutorial is broken down into three main sections: Preprocessing the hand-drawn shapes. Training a neural network to recognize these shapes. Creating a simple user interface to predict the drawn shapes. All code is available on github under the MIT License. (LINK TO REPO)    1. MNIST Maker: Preprocessing Hand-Drawn Shapes Objective : Convert the images of hand-drawn shapes into a standardized format for machine learning. Steps: Load the images from a specified directory. Convert them to grayscale and resize them to 28x28 pixels. Normalize the pixel values. Label the images based on their filenames. Shuffle and split the data for training, validation, and testing.  SCRIPT for creating the MNIST Dataset: import cv2 import numpy as np import os import random # Path to the shapes dir...
Read more

Teaching Computers to Play Tic Tac Toe: A Reinforcement Learning Approach

Image
Teaching Computers to Play Tic Tac Toe: A Reinforcement Learning Approach by Naomi A.  You can see the full code on GitHub: https://github.com/SQLNoggin/Smart-TicTacToe-RL_NaomiA.git This Python script implements a reinforcement learning agent to play the game of Tic Tac Toe. The agent is trained through Q-learning, a type of model-free reinforcement learning that uses a Q-table to estimate the value of taking specific actions in particular states. Let's break down the key aspects:   Overview The script uses the Tkinter library to create a graphical user interface (GUI) where the Tic Tac Toe game can be played. NumPy is used for numerical operations, and the pickle library is used to save and load the Q-table to/from a file. Constant and Hyperparameter Definitions PLAYER_X and PLAYER_O : Constants representing the two players (1 for X and -1 for O). EMPTY : Constant representing an empty cell on the Tic Tac Toe board. ALPHA : The learning rate used in the Q-learning updates. G...
Read more