April 27, 2010 at 2:37 am
Hi friends,
I just thought about the order of execution of the query when we run it.
Here is the sample table structure and the query to select record, there is no index in the table.
CREATE TABLE #T1
(
SID INT,SN VARCHAR(30),SAGE INT
)
GO
CREATE TABLE #T2
(
SID INT,SADD1 VARCHAR(30),SADD2 VARCHAR(30)
)
GO
SELECT T1.SID,T1.SN,T2.SADD1,T2.SADD2
FROM #T1 AS T1
INNER JOIN #T2 AS T2
ON T1.SID = T2.SID
WHERE T1.SN = 'XYZ'
My doubts is , when I execute the above select query, which section will be evaluated first. My assumption is it will evaluate the WHERE condition first.
But some how I came to know that the order will be like FROM/JOIN will be executed first!!!
So what Im thinking is if this is the cane if the table has millions of record, it will scan the entire table then finally only the WHERE condition will come into picture.... I really got confused...!!!!!
How is ORDER of execution is happening...?
Thanks & Regards,
MC
April 27, 2010 at 3:10 am
The main clauses in SQL are logically executed in the following order:
FROM (including JOINS)
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP
April 27, 2010 at 3:12 am
The order of execution is determined by the query optimizer. Look at the query plan and you'll find out the execution order of each step and how it is implemented.
Generally speaking, the order of execution is this:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
Hope this helps.
Gianluca
-- Gianluca Sartori
April 27, 2010 at 3:14 am
Whoops! Sorry for echoing, Ken.
-- Gianluca Sartori
April 27, 2010 at 6:47 am
And when you're unsure of what the optimizer will do with a query, take a look at the execution plan. That'll give you the information you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2010 at 8:07 am
only4mithunc (4/27/2010)... there is no index in the table.
... So what Im thinking is if this is the cane if the table has millions of record, it will scan the entire table then finally only the WHERE condition will come into picture... I really got confused...!!!!!
well... having no indexes on the table - as stated by poster - optimizer has no other choice than doing a full table scan, isn't it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 28, 2010 at 9:32 am
Thanks every one.
So do you mean to say, if the table has an index (on the column on which WHERE condition is applied ) the above order of execution will differ?
Thanks & Regards,
MC
April 28, 2010 at 11:11 am
only4mithunc (4/28/2010)
Thanks every one.So do you mean to say, if the table has an index (on the column on which WHERE condition is applied ) the above order of execution will differ?
Order of execution is kind of a bad way to phrase it. This is the logical order of evaluation of a query. The order of execution is best represented by what occurs within the execution plan. So, while a WHERE clause is determined after a FROM clause, within the execution plan itself, the FROM and the WHERE can be applied at the same time, in terms of an index seek, or scan, with a predicate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2010 at 11:35 am
Ok.. I understand.
Thanks & Regards,
MC
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply