September 4, 2008 at 3:47 am
All,
One of my friend has taken interview on .NET recently. Interviewer asked some questions from SQL.
He answered almost all of the questions. But he has faced one different question ( According to his perspective). He asked me the same question. As the subject explains, the question was
SELECT .....
FROM .....
WHERE ......
GROUP BY....
ORDER BY...
What is the sequence of execution ? i.e which one will execute first ?
I said the sequence as below,
1) FROM
2) WHERE
3) GROUP BY
4) SELECT
5) ORDER BY
But I may wrong. As i want to give the correct answer, I would like to ensure whether my sequence is correct or not. If not, Please give me the correct sequence with the reason.
Inputs are welcome !
karthik
September 4, 2008 at 7:19 am
According to Inside SQL Server 2005: T-SQL Querying the order of the items mentioned is:
2. WHERE
3. GROUP BY
4. SELECT
5. ORDER BY
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 3:53 am
So the sequence which i said was correct according to Inside SQL Server 2005: T-SQL Querying
karthik
September 9, 2008 at 7:07 am
It might be the 'right' answer but it is a bit simplistic.
Looking at the SELECT... If a column is not in the SELECT list or a WHERE predicate it does not need to be retrieved FROM the database. Most tables have all the data for a given row on a single page, so the presence or absence of a column in the SELECT list does not impact what is read from disk. However if you have data stored off-page (text, image, varchar(max) etc) then this data is only read from disk if it is specifically included in the SELECT list or WHERE predicate. In this case you can say part of the SELECT occurred before the FROM.
Looking at the WHERE... IMHO SQL Server orthodoxy misses a trick in talking only about sargable and non-sargable predicates. DB2 orthodoxy moved on from this many years ago and now talks about stage 0 to stage 4 predicates.
Stage 0 is satisfied in the query optimizer (e.g. 'SELECT 0 as Col1') and no data is needed from disk for this.
Stage 1 relates to sargable predicates, where the filtering is done by the relational engine (e.g. using an index to limit what is read from disk).
Stage 2 relates to non-sargable, where the filtering is done by the data manager after the data is read from disk.
Stage 3 is done by the application program, where the filtering is done after the data is returned by SQL. This is obviously bad, as more time spent writing the SQL query would have avoided getting the data in the first place.
Stage 4 is done by the user, where someone spends time scrolling down a list until they find the item they need. This is even worse, as often more time spent in the application design would have allowed the user to specify the data they wanted.
When you look at stage 0 and stage 1, then the 'right' answer does not give the actual order of execution. It is only when you look at stage 2 predicates that the 'right' answer is the actual sequence of events.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply