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 column for your customer data is populated (or not NULL).
In queries, anyone who can see the data in the table that needs to be used with some type of operator can perform operations. You need the appropriate permissions before you can successfully change the data.
Operators are used in Microsoft® SQL Server™ to:
- 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 beginning or committing a transaction, or before executing specific lines of code.
SQL Server has seven categories of operators.
| To perform this type 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, modulo. | Arithmetic operators |
| Performs an operation on one operand, such as positive or negative or one's complement. | Unary |
| Temporarily turn regular numeric values (like 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 |
| Assigns a value to a variable, or associates 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.
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.