SQL Server All-in-once features

In Select The alias created in the list cannot be in Select Used in the clause executed before the clause. In fact, expression aliases cannot be used Select Other expressions in the list. This restriction is caused SQL Another unique feature (All-at-once Operation) . For example Select List, calculate the expressionThe logical order is irrelevant.And hasUncertainty:

Select C1 + 1 as E1, C2 + 1 as E2.

Therefore, the following expression is not supported:

Select C1 + 1 as E1, E1 + 1 as E2.

You can only Select Steps after list ( For example Order Procedure ) Column alias.

 

Understanding: simultaneous operations(All-at-once Operation)

In most programming environments, we exchange the values of two variables, usually using a temporary variable:

// Assume that two assigned values exist. Int Type Variable A , B

Int temp;

Temp =;A = B; B = temp;

Of course, this is just the usual practice. You can also use the logical operators:

A = a ^ B;

B = B ^;

A = B ^;

Or:B = a + (A = B) * 0;

HoweverSQLYou can use the following statement to exchange column values in:

Update DBO. T1 set C1 = c2, C2 = C1;

Assume that all operations occur at the same time. In factThe table is not modified before the update is complete. ( It can be understood that the records in the table are locked during the update process. ) After all the results are calculated, the source data in the existing table is replaced. .

Therefore, the following update statement:

Update DBO. T1 set C1 = C1 + (select max (C1) from DBO. T1 );

This update will affect T1 All rows in the table C1 When columns are added, the update starts. T1 The largest C1 Value. In this case, the records in the table are locked after the update starts, and the database processes and computes data in the background. After all data is calculated, all data is replaced at one time. So you don't have to worry about the biggest C1 The value changes with the operation. It can be understood that the operation happened instantly.