Operators (T-SQL)

An operator is a symbol specifying an action that is performed on one or more expressions. Microsoft® SQL Server™ uses these operator categories:

• Arithmetic operators
• Assignment operator
• Bitwise operators
• Comparison operators
• Logical operators
• String concatenation operator
• Unary operators

Arithmetic Operators

Arithmetic operators perform mathematical operations on two expressions of any of the data types of the numeric data type category. For more information about data type categories.

The + and - operators can also be used to perform arithmetic operations on datetime and smalldatetime values.

For information about the precision and scale of the result of an arithmetic operation.

Assignment Operator

Transact-SQL has one assignment operator, the equals sign (=). In this example, the @MyCounter variable is created. Then, the assignment operator sets @MyCounter to a value returned by an expression.

DECLARE @MyCounter INT

SET @MyCounter = 1

The assignment operator (=) can also be used to establish the relationship between a column heading and the expression defining the values for the column. This example displays two column headings named FirstColumnHeading and SecondColumnHeading. The string xyz is displayed in the FirstColumnHeading column heading for all rows. Then, each product ID from the Products table is listed in the SecondColumnHeading column heading.

USE Northwind

GO

SELECT FirstColumnHeading = 'xyz',

       SecondColumnHeading = ProductID

FROM Products

GO

Bitwise Operators

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.

The operands for bitwise operators can be any of the data types of the integer or binary string data type categories (except for the image data type), with the exception that both operands cannot be any of the data types of the binary string data type category. The table shows the supported operand data types.

Comparison Operators

Comparison operators test whether or not two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types.

The result of a comparison operator has the Boolean data type, which has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions.

Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except an equals operator returns TRUE if both expressions are NULL. For example, NULL = NULL returns TRUE if SET ANSI_NULLS is OFF.

Expressions with Boolean data types are used in the WHERE clause to filter the rows that qualify for the search conditions and in control-of-flow language statements such as IF and WHILE, for example:

USE Northwind

GO

DECLARE @MyProduct int

SET @MyProduct = 10

IF (@MyProduct <> 0)

    SELECT *

    FROM Products

    WHERE ProductID = @MyProduct

GO

Logical Operators

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE or FALSE.

For more information about logical operators, see the specific logical operator topic.

String Concatenation Operator

The string concatenation operator allows string concatenation with the addition sign (+), which is also known as the string concatenation operator. All other string manipulation is handled through string functions such as SUBSTRING.

By default, an empty string is interpreted as an empty string in INSERT or assignment statements on data of the varchar data type. In concatenating data of either the varchar, char, or text data types, the empty string is interpreted as an empty string. For example, ‘abc’ + ‘‘ + ‘def’ is stored as ‘abcdef’. However, if the sp_dbcmptlevel compatibility level setting is 65, empty constants are treated as a single blank character and ‘abc’ + ‘‘ + ‘def’ is stored as ‘abc def’. For more information about the interpretation of empty strings..

Unary Operators

Unary operators perform an operation on only one expression of any of the data types of the numeric data type category.

The + (Positive) and - (Negative) operators can be used on any expression of any of the data types of the numeric data type category. The ~ (Bitwise NOT) operator can be used only on expressions of any of the data types of the integer data type category.

Operator Precedence

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:

• + (Positive), - (Negative), ~ (Bitwise NOT)
• * (Multiply), / (Division), % (Modulo)
• + (Add), (+ Concatenate), - (Subtract)
• =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
• ^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)
• NOT
• AND
• ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
• = (Assignment)

When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression. For example, in the expression used in the SET statement of this example, the subtraction operator is evaluated before the addition operator.

DECLARE @MyNumber int

SET @MyNumber = 4 - 2 + 27

-- Evaluates to 2 + 27 which yields an expression result of 29.

SELECT @MyNumber

Use parentheses to override the defined precedence of the operators in an expression. Everything within the parentheses is evaluated first to yield a single value before that value can be used by any operator outside of the parentheses.

For example, in the expression used in the SET statement of this example, the multiplication operator has a higher precedence than the addition operator, so it gets evaluated first; the expression result is 13.

DECLARE @MyNumber int

SET @MyNumber = 2 * 4 + 5

-- Evaluates to 8 + 5 which yields an expression result of 13.

SELECT @MyNumber

In the expression used in the SET statement of this example, the parentheses causes the addition to be performed first; the expression result is 18.

DECLARE @MyNumber int

SET @MyNumber = 2 * (4 + 5)

-- Evaluates to 2 * 9 which yields an expression result of 18.

SELECT @MyNumber

If an expression has nested parentheses, the most deeply nested expression is evaluated first. This example contains nested parentheses, with the expression 5 - 3 in the most deeply nested set of parentheses. This expression yields a value of 2. Then, the addition operator (+) adds this result to 4, which yields a value of 6. Finally, the 6 is multiplied by 2 to yield an expression result of 12.

DECLARE @MyNumber int

SET @MyNumber = 2 * (4 + (5 - 3) )

-- Evaluates to 2 * (4 + 2) which further evaluates to 2 * 6, and

-- yields an expression result of 12.

SELECT @MyNumber.