Difference in order of execution and design in SQL Server.

[WWGD]

Hi, given Sql server executes queries order from, where, group by, having, select, order by. Why don't we write queries in same order? Would that improve speed of execution ?

 

[jedishrfu]

No the sql engine constructs an execution tree and decides how to best execute the query.

Some systems have a describe or explain command that will show how the query will be executed and the actual tables it will use.

It can be quite surprising too. As an example you might have built an index table to speed your query only to discover that it’s not being used. This may mean a different index table is needed.

One caveat is it can be a negative to create too many index tables especially in a high performance application where data is added and deleted constantly. Each time you add or drop data the associated index tables needed to be added to or deleted from.

 

[WWGD]

No the sql engine constructs an execution tree and decides how to best execute the query.

Some systems have a describe or explain command that will show how the query will be executed and the actual tables it will use.

It can be quite surprising too. As an example you might have built an index table to speed your query only to discover that it’s not being used. This may mean a different index table is needed.

One caveat is it can be a negative to create too many index tables especially in a high performance application where data is added and deleted constantly. Each time you add or drop data the associated index tables needed to be added to or deleted from.

I guess that is why you do not use many indexes in OLTP setup? I mean, since they do mostly inserts, updates and deletes.

 

[WWGD]

No the sql engine constructs an execution tree and decides how to best execute the query.

Some systems have a describe or explain command that will show how the query will be executed and the actual tables it will use.

It can be quite surprising too. As an example you might have built an index table to speed your query only to discover that it’s not being used. This may mean a different index table is needed.

One caveat is it can be a negative to create too many index tables especially in a high performance application where data is added and deleted constantly. Each time you add or drop data the associated index tables needed to be added to or deleted from.

It seems from doing informal research that SQL Server will not execute queries that are not done in the order : SELECT FROM WHERE GROUP BY HAVING ORDER BY. I get error messages every time I try any query that violates this order sequence.