T-SQL All-at-once Operation parsing

SQL supports the concept of a so-called simultaneous operation, meaning that all expressions that occur during the same logical query processing phase are computed at the same time. For example, this concept can explain why you cannot reference the alias assigned to a column in the same SELECT clause in the SELECT clause, even if you intuitively seem to be able to do so. Consider the following query:

SELECT      OrderID,    year as  orderyear,     +1 as   from

The third expression in the select list above is not valid for a reference to the OrderYear column alias, even if the reference expression is behind the definition of the alias. Because logically, the calculations of the expressions in the select list are not sequential-they are just a set of expressions. All the expressions in the logically select list are
is calculated at the same time.

One more example: Suppose you have a table called T1, which has two integer columns: coll and col2. Now you want to return all rows with Col2/coll greater than 2. Because the coll column of some rows in the table might be equal to 0, you need to make sure that you cannot perform a division on those columns, or the query will fail because of a divisor of 0 errors. To do this, there are the following query statements:

  SELECT from WHERE col1 <> 0 and col2/  >2

This statement assumes that SQL Server evaluates each expression in left-to-right order, and if the result of the expression col1<>0 is False,sql Server will stop evaluating the expression according to the principle of "short-circuit evaluation": that is, It will no longer superfluous the expression col2/col1 > 2, as it is now known that the entire
The result of an expression is false. So you might think that this query should never have a divisor of 0, but it's not.

SQL Server does support "short-circuit evaluation", but because ANSI SQL has a "simultaneous operation" concept, SQL Server can freely handle the expressions in the WHERE clause in whatever order it likes. For this type of problem, SQL Server usually makes decisions based on the criteria of the cost estimate, that is, the expression is usually calculated at a lower cost. As you can see, if SQL Server decides to process the expression col2/col1 > 2 First, the query may fail with a divisor of 0 errors.

To avoid query execution failures as much as possible, there are several approaches here. For example, when clauses in a case expression are evaluated in a guaranteed order. Therefore, the above query can be modified as follows:

SELECTColl, col2 fromdbo. T1WHEREE Case          whenCol1= 0           Then'No'             whenCol2/Col1> 2     Then'Yes'         ELSE'No'     END ='Yes';

If the col1 column of a row equals 0, the first when clause evaluates to a true,case expression that returns the string ' No ' (if the row is returned when col1 equals 0 o'clock, you can change no ' to ' yes '). The second when clause checks if the result of the expression Col2/col1> 2 is true only if the first case expression evaluates to a range of true (that is, Coll is not 0). Returns the string ' Yes ' if it is an true,case expression. For all other cases, the case expression returns the string ' No '. The predicate in the WHERE clause returns true only if the case expression evaluates to equal to the string ' yes '. In this way, there is no doubt that the divisor is 0 error in an expression.

This solution is actually very shivering, and in this particular case, we can use a simpler mathematical approach to avoid a divisor of 0 errors:

 SELECT from WHERE coll <> 0 and >  2*

The sample code explains the unique and important concept of "simultaneous operation" of SQL, and the fact that SQL Server can ensure that the When clause in a case expression is processed in the same order.

　

All right, this article is introduced here, welcome to the message exchange, like or helpful to your words, order a praise or recommend support!　

T-SQL All-at-once Operation parsing