How query works in 2005

Question

How query works in 2005

nkm129

SSCommitted

Points: 1763
More actions
September 9, 2008 at 8:17 am

#192633

Hi All,
First of all thanks for all you guys.. You people are super..
1) Now could someone please tell what happens when we run any select query in back of sql server 2005. How sql will work.
Like :-
Select * from emp
How it process and one more thing
2)What is hierarchy in sql server 2005.
Means if you have
select * from emp where column1 = '' " group by column2, order by column3
in this case what thing happen first. Does group by happen before order by or like that.
Please suggest me any blog or any article to understand these concept.
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 1

Not sure what you mean by "how it process". You can get the execution plan, which will tell you what work is being done. Queries can be processed different ways, so it depends on your system.

GROUP BY comes before ORDER BY.

thegreatsamarth SSChasing Mays Points: 608 More actions · Answer 2

Below is a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed:

(8) SELECT (9) DISTINCT (11) (TOP_specification) (select_list)

(1) FROM (left_table)

(3) (join_type) JOIN (right_table)

(2) ON (join_condition)

(4) WHERE (where_condition)

(5) GROUP BY (group_by_list)

(6) WITH {CUBE | ROLLUP}

(7) HAVING (having_condition)

(10) ORDER BY (order_by_list)

Each step generates a virtual table that is used as the input to the following step. These virtual tables are not available to the caller (client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in a query, the corresponding step is simply skipped.

So, finally this is the hierarchy in which any SQL query processed:

1. FROM:

2. ON:

3. OUTER (join):

4. WHERE:

5. GROUP BY:

6. CUBE | ROLLUP:

7. HAVING:

8. SELECT:

9. DISTINCT:

10. ORDER BY:

11. TOP:

--Samarth

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

The best description of this I've seen is contained in the first two chapters of Itzik Ben-Gan's book, Inside SQL Server 2005: T-SQL Querying.

"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

nkm129 SSCommitted Points: 1763 More actions · Answer 4

Thanks to all.. I got book what is suggested by grant ..

Thanks!