Microsoft SQL Server has a range of operators that can be used in SQL queries to perform various operations. Here’s a rundown of some key operators, along with examples for each:

1. Arithmetic Operators

These operators are used for mathematical calculations.

• Addition (+):

  SELECT 5 + 3 AS AdditionResult;

  Result: 8

• Subtraction (-):

  SELECT 10 - 4 AS SubtractionResult;

  Result: 6

• SELECT 7 * 3 AS MultiplicationResult;

  Result: 21

• Division (/):

  SELECT 20 / 4 AS DivisionResult;

  Result: 5

• Modulus (%) (remainder of division):

  SELECT 17 % 5 AS ModulusResult;

  Result: 2

2. Comparison Operators

These operators compare two values and return a boolean result.

• Equal to (=):

  SELECT CASE WHEN 5 = 5 THEN 'True' ELSE 'False' END AS IsEqual;

  Result: True

• Not equal to (<>):

  SELECT CASE WHEN 5 <> 3 THEN 'True' ELSE 'False' END AS IsNotEqual;

  Result: True

• Greater than (>):

  SELECT CASE WHEN 7 > 3 THEN 'True' ELSE 'False' END AS IsGreaterThan;

  Result: True

• Less than (<):

  SELECT CASE WHEN 3 < 7 THEN 'True' ELSE 'False' END AS IsLessThan;

  Result: True

• Greater than or equal to (>=):

  SELECT CASE WHEN 5 >= 5 THEN 'True' ELSE 'False' END AS IsGreaterOrEqual;

  Result: True

• Less than or equal to (<=):

  SELECT CASE WHEN 3 <= 5 THEN 'True' ELSE 'False' END AS IsLessOrEqual;

  Result: True

3. Logical Operators

These operators are used to combine multiple conditions.

• AND:

  SELECT CASE WHEN 5 > 3 AND 10 < 15 THEN 'True' ELSE 'False' END AS IsAndConditionTrue;

  Result: True

• OR:

  SELECT CASE WHEN 5 > 3 OR 10 > 15 THEN 'True' ELSE 'False' END AS IsOrConditionTrue;

  Result: True

• NOT:

  SELECT CASE WHEN NOT (5 > 10) THEN 'True' ELSE 'False' END AS IsNotConditionTrue;

  Result: True

4. Bitwise Operators

Used for bit-level operations.

• Bitwise AND (&):

  SELECT 12 & 5 AS BitwiseAndResult;

  Result: 4

• Bitwise OR (|):

  SELECT 12 | 5 AS BitwiseOrResult;

  Result: 13

• Bitwise XOR (^):

  SELECT 12 ^ 5 AS BitwiseXorResult;

  Result: 9

• Bitwise NOT (~):

  SELECT ~12 AS BitwiseNotResult;

  Result: -13

• Bitwise Shift Left (<<):

  SELECT 3 << 2 AS BitwiseShiftLeftResult;

  Result: 12

• Bitwise Shift Right (>>):

  SELECT 12 >> 2 AS BitwiseShiftRightResult;

  Result: 3

5. String Operators

Used for string manipulation.

• Concatenation (+):
  SELECT 'Hello' + ' ' + 'World' AS ConcatenatedString;
  Result: Hello World

6. Null Operators

Used to handle NULL values.

• IS NULL:

  SELECT CASE WHEN NULL IS NULL THEN 'True' ELSE 'False' END AS IsNullResult;

  Result: True

• IS NOT NULL:

  SELECT CASE WHEN 5 IS NOT NULL THEN 'True' ELSE 'False' END AS IsNotNullResult;

  Result: True

• COALESCE (returns the first non-NULL value):

  SELECT COALESCE(NULL, 'Default Value') AS CoalesceResult;

  Result: Default Value

• NULLIF (returns NULL if the two values are equal):

  SELECT NULLIF(5, 5) AS NullifResult;

  Result: NULL

  SELECT NULLIF(5, 6) AS NullifResult;

  Result: 5

7. Other Operators

• EXISTS (used in subqueries to check for the existence of rows):

  SELECT CASE WHEN EXISTS (SELECT * FROM Employees WHERE Salary > 50000) THEN 'Exists' ELSE 'Not Exists' END AS ExistsCheck;

  Result: Depends on the data in the Employees table.

• BETWEEN (checks if a value is within a range):

  SELECT CASE WHEN 10 BETWEEN 5 AND 15 THEN 'Within Range' ELSE 'Out of Range' END AS RangeCheck;

  Result: Within Range

• IN (checks if a value is in a set of values):

  SELECT CASE WHEN 3 IN (1, 2, 3, 4) THEN 'In Set' ELSE 'Not In Set' END AS InSetCheck;

  Result: In Set

• LIKE (used for pattern matching in strings):

  SELECT CASE WHEN 'Hello World' LIKE '%World%' THEN 'Pattern Found' ELSE 'Pattern Not Found' END AS LikePatternCheck;

  Result: Pattern Found

• ISNULL (replaces NULL with a specified value):

  SELECT ISNULL(NULL, 'Default Value') AS IsNullResult;

  Result: Default Value

• CAST/CONVERT (used for data type conversion):

  SELECT CAST(123.456 AS INT) AS CastResult;

  Result: 123

  SELECT CONVERT(VARCHAR, GETDATE(), 101) AS ConvertResult; -- Converts current date to mm/dd/yyyy format

This list covers most of the common operators used in Microsoft SQL Server. Each operator is essential for performing various types of operations and transformations on data.