Bitwise Operators
Bitwise Operators
Bitwise operators perform bit operations on integer data types; all bitwise operators introduced into SQL by RDBMS vendors are listed in Table 11-6. To understand the results of the bitwise operations one must understand the basics of Boolean algebra.
| Cross-References |
See Appendix L for more information on Boolean algebra. |
|
Operator |
Description |
|---|---|
|
& |
Bitwise AND |
|
| |
Bitwise OR |
|
/\ |
Bitwise exclusive OR |
|
~ |
Bitwise NOT |
The operands for bitwise operators can be of either the integer data type or the binary string data type (except for IMAGE data type) category. Data type compatibility is given in Table 11-7. Bitwise operations are not typical for a high-level language such as SQL, and one might be hard-pressed to come up with a usage example.
|
Left Operand |
Right Operand |
|---|---|
|
BINARY |
INT, SMALLINT, TINYINT |
|
BIT |
INT, SMALLINT, TINYINT, BIT |
|
INT |
INT, SMALLINT, TINYINT, BINARY, VARBINARY |
|
SMALLINT |
INT, SMALLINT, TINYINT, BINARY, VARBINARY |
|
TINYINT |
INT, SMALLINT, TINYINT, BINARY, VARBINARY |
|
VARBINARY |
INT, SMALLINT, TINYINT |
One of the possible uses could be a complex bit mask made for color — after all RDBMS now supports more than just text and numeric data. Consider a combination of zeroes and ones, for example 0101. When a binary AND is applied to this number and to another binary number, for example 1101, it will produce the following result:
SELECT 5 & 13 result result ------ 5
The binary representation of 5 is 0101. In this example of logical AND, only 1 and 1 produce 1, any other combinations — namely 1 and 0, or 0 and 0 — produce 0.
0101 (decimal 5) 1101 (decimal 13) ---- 0101 (decimal 5)
To decode or encode a pixel in an image stored in your database you would use bit mask and shifting. Another possible use of the XOR (exclusive OR) operator would be to encrypt data based on some numeric key.
 |
In the database world, safety of data is of paramount importance; it comes right after getting the software running at all. There are different safety aspects, discussed in Chapter 12, including unauthorized access, malicious data manipulations, and so on. Here we're going to briefly touch the data corruption issue.
Sometimes a technical glitch occurs and data gets corrupted — somewhere in the gazillions of zeroes and ones representing data on your hard drive, a couple of bits get flipped, exchange places, or disappear altogether. You could blame it on a power fluctuation, cosmic rays, earthquakes, and such, but the question remains — how to restore data to a consistent state.
One of the technologies out there is RAID (Redundant Array of Independent Disks). While RAID does not apply directly to SQL, it does have something important to do with RDBMS and Boolean algebra.
The idea behind RAID is to combine several relatively inexpensive disk drives into an array that works as a single unit, thus increasing the reliability and speed of operations. There are different types of RAID architecture (ways these disks can be combined), ranging from RAID0 to RAID5 (the first, RAID0, being just another name for a single disk).
What makes RAID relevant to Boolean logic is that the fundamental technology behind it is striping. RAID partitions each of the drives participating in the array into stripes that are interleaved in a sequence.
To visualize that, imagine a stack of wafers where you butter the first, third, and fifth wafers, and spread jam over the second and fourth. In a similar manner, RAID enables you to perform different operations on the data spread across more than one disk (drive) simultaneously, greatly improving input/output operations. In RAID3, RAID4, and RAID5 configurations, an additional benefit of safety comes from parity, where one drive is dedicated to storing parity information: In case of one drive failure the whole information can be recovered by using a Boolean exclusive OR (XOR) operation of the bit information on the remaining drives.
Consider the previous example, but performing only an XOR operation instead of the logical AND. The XOR operation follows rules of Boolean logic: 0 XOR 0 = 0, 0 XOR 1 = 1, 1 XOR 1 = 0, and 1 XOR 0 = 1. Imagine that the first number is written on disk 1, the second is written on disk 2, and their XOR result (parity) — on disk 3. Now, disk 2 (containing value 1101) has failed. By performing an XOR operation between data on disk 1 and data on disk 3, you could easily restore the corrupted values:
0101 0101 1101 1000 ---- ---- 1000 1101
Of course, if more than one drive fails, then this safety system would not work. The bet is on the calculated risk, when failure of more than one drive at the same time is extremely unlikely.
All this is a fairly low level that normally is outside of the SQL programming domain, and chances are that if you find yourself in need of more in-depth coverage of Boolean algebra, you will reach for a topic-specific book, and not the one on general SQL issues.
|
Only Microsoft SQL Server provides bitwise operators; the IBM DB2 UDB dialect of SQL does not have bit operations support built into the language itself, and Oracle 9i has a BITAND function that works identically to SQL Server's bitwise AND.
