Microsoft® SQL Server® 2012 Bible

181

Chapter 8: Data Types, Expressions, and Scalar Functions

8

SELECT 1'1;

Result:

0

A set bit XORed with a cleared bit results in a set bit:

SELECT 1'0;

Result:

1

XORing two integers can be illustrated as follows:

decimal 3 = binary 011

decimal 5 = binary 101

3 OR 5

decimal 6 = binary 110

Bitwise not

The last bitwise operator, denoted by the tilde (~), is a bitwise NOT function. This bitwise

“not” is a little different. The “not” performs a logical bit reversal for every bit in the

expression. The result depends on the data length of the expression. For example, the bit-

wise “not” of a set bit is a cleared bit:

DECLARE @A BIT;

SET @A = 1;

SELECT (~)@A;

Result:

0

The bitwise “not” is not suitable for use with boolean expressions such as IF conditions.

The following code, for example, is invalid:

USE AdventureWorks

SELECT * FROM Production.Product WHERE < (1=1);

The “not” operator also serves as the one’s complement operator. The system known as one’s

complement can be used to represent negative numbers. The one’s complement form of a

negative binary number is the bitwise NOT applied to it — the complement of its positive

counterpart.

Case Expressions

SQL Server’s CASE expression is a fl exible and excellent means of building dynamic

expressions. If you’re a programmer, no doubt you use the case command in other lan-

guages. The SQL CASE expression, however, is different. It’s not used for programmatic

fl ow of control, but rather to logically determine the value of an expression based on a

condition.

c08.indd 181c08.indd 181 7/30/2012 4:21:13 PM7/30/2012 4:21:13 PM

http://www.it-ebooks.info