As a SQL Server database programmer or implementer, you sometimes need to accommodate several two-valued entity attributes—or flags—when you create tables. Some examples of two-valued attributes are on/off, true/false, male/female, and married/single. SQL Server supplies the data type bit with which you can represent two-valued attributes—or three-valued, if you count NULL as a value. However, if you have many flags of this nature to store, you can end up with a lot of bit columns in your table and a lot of parameters in your stored procedures or user-defined functions (UDFs) that manipulate them. Furthermore, SQL Server 7.0 doesn't let you create an index on a bit column; this limitation in 7.0, among other reasons that apply to all versions, might mean that you'd choose to store flags in integer columns instead of storing them in bit columns. Fortunately, SQL Server 2000 lets you create indexes on bit columns, so indexing isn't a concern if you use SQL Server 2000.
If you have lots of flag values to store and you don't want so many columns in your tables, you might prefer to store several flag values in one binary or integer column. SQL Server stores integer or binary values (as it stores values of any other data type) internally as a series of bits in which each bit is either turned on (1) or turned off (0). In fact, SQL Server stores many flag values in various columns in system tables. For example, the status column in the sysindexes system table is an integer column that stores information about each index's characteristics in several bits (a bitmap). One bit in the status column specifies whether the index is unique, another bit specifies whether the index is clustered, and so on. If you want to manipulate your own bitmaps (e.g., to determine whether a certain bit is on or off or to update the value of a certain bit), you need to use bitwise operators.
Bitwise operations perform operations on bits. The bitwise operators you use in T-SQL are the same operators that you use in electronics: the binary AND (&), OR (|), and XOR (^) operators and the unary NOT (~) operator. Binary bitwise operators (AND, OR, and XOR) work on (i.e., involve in the calculation) two bits and return a result bit according to the truth table defined for them in SQL Server. A truth table contains all possible combinations of the input bits, and the result of the bitwise operation for each combination. Unary operators—in T-SQL, only the NOT operator—operate on one bit and return a result bit according to the truth table defined for them in SQL Server.
Bitwise Operators' Validity
Before you try to use bitwise operators, you need to know that they don't accept all combinations of data types for operands (arguments that the operator works on). Tables 1 and 2 list the data-type combinations that bitwise operations accept.
For example, suppose you want to determine whether you can perform a bitwise AND operation between two binary values. Table 1 shows an error at the intersection of binary and binary—so, an attempt to perform a bitwise operation between two binary values will result in an error. However, the intersection of int and binary is valid—so a bitwise AND operation between an integer value and a binary value produces a result value.
As surprising as it might seem, the binary bitwise operators (AND, OR, and XOR) don't allow both arguments to be binary data types, and the unary bitwise operator (NOT) doesn't allow its argument to be a binary data type. The fact that you can't perform a bitwise operation between two binary values can be a limitation in some situations. For example, you can't perform a bitwise operation between two values when one value is larger than 8 bytes (which is the largest integer to which a binary value can be converted) without truncating some of the larger value's data. To overcome this problem, you need to convert one of the input binary arguments to a data type that will result in one of the valid argument combinations.
Consider the following example. Suppose you want to perform a bitwise AND operation between two binary values. (Note that the binary values in the following query are represented in hexadecimal base; four binary bits are represented by one hex digit; a pair of hex digits make a byte.) The following statement will fail:
SELECT 0x00000001 & 0x00000001
Consulting the validity tables, you discover that you can't perform a bitwise AND between two binary data types. However, you can perform the AND operation between a binary data type and an integer data type, so you can convert one of the arguments to an integer data type, as follows:
SELECT CAST(0x00000001 AS int) & 0x00000001
Regardless of the arguments' data types, the result of a bitwise operation in SQL Server always returns a value that's an integer data type. In next month's T-SQL Solutions feature article, I'll show you how to use UDFs to solve this problem.
SQL Server doesn't support bitwise operations between two binary values; one of the values has to be an integer data type. The reason for this limitation is probably that the designers included support for bitwise operations so that you can manipulate strings of bits stored in individual integer columns instead of in multiple bit columns. But as soon as Microsoft added support for bitwise operations to SQL Server, programmers wanted to use the operators for purposes that SQL Server wasn't designed for. For example, programmers might want to manipulate bitmaps longer than the largest integer or use the bitwise XOR operator to encrypt binary values using a binary key that's longer than the largest integer value.
Prev. page
[1]
2
3
next page 
