What does "&" means in this sql where clause?

Question

What does the "&" mean here:

select pt.TrTp, sum(pt.TrTp)
from ProdTr pt
where TransSt & 16 <> 16  // this is the row that i don´t understand..
group by pt.TrTp

It's a bitwise AND. More info: http://msdn.microsoft.com/en-us/library/ms174965.aspx — J. Steen, Mar 08 '13 at 08:48

score 7 · Accepted Answer · answered Mar 08 '13 at 08:54

7

It's called a bitmask. It is used in situations where the individual bits in a number have different meanings, as opposed to a number just meaning the number itself (for instance, if you save your age to the database).

When you imagine any number in it's binary form and want to test if a certain bit in the number is set, you test it by using the binary AND operator with the number and the bit you want to test, like this:

if (number & 16 == 16)

In binary, this means the following (assuming, your number is 25):

if (00011001 & 00010000 == 00010000)

Here you can see, that the digits at the bit 5 (counted from the bottom up) are both 1, therefor the resulting number has a 1 at that bit. As there are no other 1s, the resulting number is 16 exactly when both numbers have a 1 at this position.

I would like to add: It's usually bad practice to encode different meanings into one database field. It's difficult to index and retrieve by an index, and depending on your DBMS might even be completely unindexed.

answered Mar 08 '13 at 08:54

0xCAFEBABE

5,576
5
34
59

ok, but couldn´t this where clause be rewritten in any "simpler" form? – daniel_aren Mar 08 '13 at 09:01
Not for the given database structure. Somebody decided that (s)he wanted to encode information into single bits inside the number. You can't get around that unless dividing the single digit information to columns, i.e. refactoring the database. I'd highly suggest this, as modern DMBSes are much better at optimizing than most humans are nowadays. – 0xCAFEBABE Mar 08 '13 at 09:04
@0xCAFEBABE I'd be really interested in any papers or such written on the subject that would explain why bitmasks (in this case status bitmasks that don't require you to change the database when you add a status-type) aren't worth it any more. What would you use instead? – J. Steen Mar 08 '13 at 09:07
i my case the "TransSt" will always have a int from 0 - 15, then won´t this always be false? – daniel_aren Mar 08 '13 at 09:17
@J.Steen It's a complex topic, and it's highly dependent on how the flag number/byte/long is read. If you make sure that it's never read as part of a query and is only used as part of the record itself, it shouldn't be a problem. However, an index over the individual bit values can never be buildt, so querying the value using bit operations will force the DMBS to complete the bit operation for all rows matching all other criteria, possibly even preventing it from using an index at all. I have not done extensive tests with this, and I would be interested as well in some EXPLAIN structures. – 0xCAFEBABE Mar 08 '13 at 09:19
@0xCAFEBABE Your reasoning on the performance of an index (where it can never be reliably applied) seems rather logical. And that would be very detrimental on a huge table. Thanks for the insight! I also found [this answer](http://stackoverflow.com/a/5708369/64976) on the topic at hand. – J. Steen Mar 08 '13 at 09:22
@J.Steen Thanks for digging this up. – 0xCAFEBABE Mar 08 '13 at 09:30

score 1 · Answer 2 · answered Mar 08 '13 at 08:49

1

This is bitwise AND. It simply checks if bit 4 in value is NOT set.

I would rewrite this expression as TransSt & 16 = 0

answered Mar 08 '13 at 08:49

mvp

111,019
13
122
148

score 0 · Answer 3 · answered Mar 08 '13 at 08:49

It's a bitwise AND.

The & bitwise operator performs a bitwise logical AND between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0.

From http://msdn.microsoft.com/en-us/library/ms174965.aspx

In this case it's used with a flag, where multiple values are stored in one field and then the bitwise AND operation is used to check for specific states - or in this particular case - check that the field doesn't contain the specified state.

score 0 · Answer 4 · answered Mar 08 '13 at 08:50

0

It's bitwise AND. Please refer to this MSDN Article.

answered Mar 08 '13 at 08:50

Andrew Savinykh

25,351
17
103
158

score 0 · Answer 5 · answered Mar 08 '13 at 08:50

It's a bitwise AND operator.

A bitwise operation operates on one or more bit patterns or binary numerals at the level of their individual bits. It is a fast, primitive action directly supported by the processor, and is used to manipulate values for comparisons and calculations. On simple low-cost processors, typically, bitwise operations are substantially faster than division, several times faster than multiplication, and sometimes significantly faster than addition. While modern processors usually perform addition and multiplication just as fast as bitwise operations due to their longer instruction pipelines and other architectural design choices, bitwise operations do commonly use less power/performance because of the reduced use of resources.

From http://en.wikipedia.org/wiki/Bitwise_operation

What does "&" means in this sql where clause?

5 Answers5

Linked

Related