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.