T-SQL Expressions

An expression is a syntactical element or clause composed of identifiers, operators, and values that can evaluate to obtain a result. Like a sentence consisting of subject, verb, object to convey an action, the expression must be logically complete before it can compute. In other words, the elements of an expression must “add up.” In the general programming environments, an expression will always evaluate to a single result. However, Transact-SQL expressions are evaluated individually for each row in the result set. In other words, a single expression may have a different value in each row of the result set, but each row has only one value for the expression.

The following T-SQL elements are expressions:

• A function, such as DB_ID(), is an expression because it computes to return a value that represents a database ID.

• A constant is an expression because it alone represents a value.

• A variable is an expression for the same reason a constant is.

• A column name is an expression because it too represents or evaluates a value.

• A subquery is an expression because it computes or evaluates a result.

• Mathematical operations are expressions; for example, 1+1=2 or total*6/100.

• CASE, NULLIF, and COALESCE are expressions (discussed a little later in this chapter).

The preceding list items are known as simple expressions. When you combine two or more simple expressions with operators, you get a complex expression. A good example of complex expression is your average SELECT statement. For example, SELECT * FROM CITY is a complex or compound expression because the statement can return the name of a city for each row in the table.

It is possible to combine expressions using an operator, but only if the expressions have data types that are supported by the operator. In addition, the following rules also apply:

• A data type that has a lower precedence can be implicitly converted to the data type with the higher data type precedence.

• Using the CAST function, you are able to explicitly convert the data type with the lower precedence to the data type with the higher precedence. Alternatively, you should be able to use CAST to convert the source data type to an intermediate data type, and then convert the intermediate data type to the data type with the higher precedence.

If you are unable to perform either an implicit or explicit conversion, then you cannot combine the two expressions to form a compound expression.

Expression Results

In addition to the preceding rules, the following also applies to SQL Server expressions:

• When you create a simple expression comprising a single variable, a constant, a scalar function, or a column name, the data type, the collation, the precision and scale, and the value of the expression are the data type, collation, precision, scale, and value of the referenced element.

• When you combine two expressions with comparison or logical operators, the resulting data type is Boolean and the value is one of TRUE, FALSE, or UNKNOWN. (See “Comparison Operators” in the next section).

• When you combine two expressions with arithmetic, bitwise, or string operators, the operator determines the resulting data type.

• When you create compound expressions, comprising many operators, the data type, collation, precision, and value of the resulting expression are determined by combining the component expressions, two at a time, until a final result is reached. The sequence in which the expressions are combined is defined by the precedence of the operators in the expression.