Using Operators in Expressions

Operators allow you to perform arithmetic, comparison, concatenation, or assignment of values. For example, you can test data to verify that the country/region column for your customer data is populated, or not NULL.

In queries, anyone who can see the data in the table that should be used with some type of operator can perform operations. You must have the appropriate permissions before you can successfully change the data.

Operators are used in Microsoft SQL Server to do the following:

  • Change data, either permanently or temporarily.

  • Search for rows or columns that meet a specified condition.

  • Implement a decision between columns of data or between expressions.

  • Test for specific conditions before starting or committing a transaction, or before executing specific lines of code.

SQL Server has the following categories of operators.

To perform this kind of operation

Use this operator category

Compare a value against another value or an expression.

Comparison operators

Test for the truth of a condition, such as AND, OR, NOT, LIKE, ANY, ALL, IN.

Logical

Addition, subtraction, multiplication, division, and modulo.

Arithmetic operators

Perform an operation on one operand such as positive or negative, or on the complement.

Unary

Temporarily turn regular numeric values, such as 150, into an integer and perform bitwise, 0 and 1, arithmetic.

Bitwise Operators

Either permanently or temporarily combine two strings, either character or binary data, into one string.

String Concatenation Operator

Assign a value to a variable, or associate a result set column with an alias.

Assignment

An expression can be built from several smaller expressions combined by operators. In these complex expressions, the operators are evaluated in order based on the SQL Server definition of operator precedence. Operators with higher precedence are performed before operators with lower precedence. For more information, see Operators (Transact-SQL).

When simple expressions are combined to form a complex expression, the data type of the result is determined by combining the rules for the operators with the rules for data type precedence. If the result is a character or Unicode value, the collation of the result is determined by combining the rules for the operators with the rules for collation precedence. There are also rules that determine the precision, scale, and length of the result, based on the precision, scale, and length of the simple expressions.