“TSQL is 2 faced!” – cried the app team member
“Have you been watching Batman?”
Recent conversation with application folks made me think and I decided to break it down on Understanding Logical Query Processing (LQP).
- Logical – is the conceptual understanding of the statement and what will be the result set
- Physical – is what is processed by the Engine; ensuring that end results stick to the logical concept
This will be my attempt to take a swing at (on a high level) how a query is visualized by the Engine and in turn visualized by a DBA. Going back and forth my engineering friends from UT Arlington on how they would perceive “Get me a bottle of water from the table”, the end result “Go to table, get water bottle, get it to me.” Same concept gets applied when a TSQL is sent across.
For instance, TSQL follows the English language pattern
[crayon class=”lang:tsql decode:true”]
SELECT emplId, fName, lName, phone
FROM dbo.Employee
[/crayon]
But LQP interprets as
FROM dbo.Employee SELECT emplId, fName, lName, phone
TSQL dictates the following pattern:
- Select
- From
- Where
- Group By
- Having
- Ordered By
LQP dictates the following pattern:
- From
- Where
- Group By
- Having
- Select
- Ordered By
Let’s take a step down further:
- FROM – This is where you indicate the table(s) you want to query
- WHERE – There is where the FILTER process starts. Rows are filtered based on the predicate. Note: References to COLUMN ALIAS will error out as WHERE is evaluated prior to SELECT.
- GROUP BY – This defines a group for each distinct value to be clubbed. If a reference is made to a value that is not part of the GROUP BY it needs to be part of the aggregate family (SUM, MAX)
- HAVING – Another filter to further refine your result set. Since this is after GROUP BY, HAVING evaluates per group and filters groups as whole.
- SELECT – Finally the SELECT gets evaluated. It makes sure that it evaluates the list and display results based on that. If a DISTINCT is applied, it takes one more step and removes duplicates from the result set.
- ORDERED BY – Presentation is everything. If ORDERED BY clause is used, it will present the result set accordingly. Also note ORDER BY can be done on COLUMN ALIAS since this is done after the SELECT clause.
With this in mind, it will help newbies to tackle pages of codes when troubleshooting.
~ Adios
Khan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
