Hi, I have to decide the way to perform a where on a field of a table using bitwise operation. I'll try to explain: I have a table with an indexed int field where I store a code whose value is in a set of power-of-2 codes, i.e. Messages Table IDMessage bigint MessageType int The allowed values of Message Type are pre-defined: 1 - Private Message 2 - Public Message 4 - Broadcast Message 8 - Open Message and so on... Suppose I have to select the rows of type Private OR Broadcast = 1 | 4 = 5 I could do SELECT * FROM Messages WHERE (5 & MessageType) = MessageType or SELECT * FROM Messages WHERE MessageType = 1 OR MessageType = 4 My doubt is: Is the first query efficient? Or does it implies a table scan to perform a bitwise operation for each row in the table? I have absolutely no idea on the effect of such a type of comparison on indexed field... Thank you very much in advance for any elucidation!
Using OR condition is definitly better as you can use if you have an index on it. Additionally, it is easy to read, debug and maintain.
[quote user="lookaround"] Suppose I have to select the rows of type Private OR Broadcast = 1 | 4 = 5 I could do SELECT * FROM Messages WHERE (5 & MessageType) = MessageType or SELECT * FROM Messages WHERE MessageType = 1 OR MessageType = 4 [/quote] SELECT * FROM Messages WHERE (5 & MessageType) = MessageType , return a mesaage that is both: Private Message and Broadcast Message which is not: SELECT * FROM Messages WHERE MessageType = 1 OR MessageType = 4 , don't return the value stored in MessageType field is 5, and by bit operation to mask bits needed as you do bit operation on the field (i.e you do compuation on the field in where condition), i think optimizer will do index scan (not index seek) generate execution plan to be sure.
Thanks for your suggestion. The messageType stored value can be only one; can't be a combination of the flags (i.e. each Row is of a unique messagetype) I'll store in MessageType Field only one of the allowed values defined by the flag (i.e. one of 31 allowed values). When selecting I'll pass, tipically in a stored procedure paramater, a int value which can be a combination of Flags. So If I want to select the messages of type Private and Broadcast I'll pass the value 5 in the sp parameter and then perform the bitwise comparison. Due to the behaviour explained, the index should have only maximum 31 entries (one for each of the defined value of the flag). Is this correct? In your opinion, basing on the fact that index entries are in a limited number, do you think the index scan will be efficient? Thanks again in advance!
[quote user="lookaround"]Due to the behaviour explained, the index should have only maximum 31 entries (one for each of the defined value of the flag). Is this correct?[/quote] you are correct bez, your where condition is: where messagetype = @myaparam (the parameters you pass in sp) , you select one out 31 indexed values , so index is seek , and performance is good. But, if your where condition is: where mesagetype & value1 = value1 , you do computation in the field , and optimizer do index scan (less efficient) and avoid this, as you can