Practical Bit mask in SQL Server
Effective, simple, and fun.
I always enjoy doing some ‘practical bitmasking’ whenever I can, if for no other reason than to show someone that there are some cool situations to use the technique. If you’ve happened to catch some of my other blogs or presentations on bitmasking, you know that it’s very slick and a great way to solve a wide array of problems. Would you find a place to use this in every day coding? Of course not. Every month? I doubt that very seriously as well. However, when you do find a neat place to use it, you can show off your computer science savvy to your friends and family and appreciate the greatness of masking.
I just ran into a 1000 + line trigger that interrogated, for each country_id, 18 or 20 attributes, checking for the existence of a flag in each attribute. If any of the attributes – at least one – was turned on, then the extract_flag should be set to true. This flag indicated that, yes, let’s go ahead and download the 200 MB file from the AS/400. A worthwhile endeavor since if no flag which represented a process was set to true, then the download was skipped because the file wasn’t being used at that time. Not a bad setup for sure, except for the 1000+ line trigger of course; who wants to download a bunch of files that will sit there and not be used?
Let’s set up the data and take a look at the process similar to mine at work:
CREATE TABLE Config
(ID int PRIMARY KEY NOT NULL
,Extract_Flag bit
,A_val bit DEFAULT 0
,B_val bit DEFAULT 0
,C_val bit DEFAULT 0
,D_val bit DEFAULT 0
,E_val bit DEFAULT 0
,F_val bit DEFAULT 0
,G_val bit DEFAULT 0
,H_val bit DEFAULT 0
)
GO
INSERT INTO Config (ID) VALUES (100)
INSERT INTO Config (ID) VALUES (200)
INSERT INTO Config (ID) VALUES (300)
GO
UPDATE Config
SET B_val = 1
WHERE ID=100
UPDATE Config
SET C_val = 1
WHERE ID=100
UPDATE Config
SET H_val = 1
WHERE ID=100
UPDATE Config
SET A_val = 1
WHERE ID=200
SELECT * FROM Config
So, similar situation here – I’ve got some flags for an ID, and if any one of them are set to true, make sure that the Extract_Flag attribute is also set to true. In the case of ID 300, none of the flags are turned on, meaning that each of the processes have been shut off, so the source file download can be skipped. The nasty trigger which I speak so highly of was an FOR UPDATE trigger that checked all of the attribute flags, and if all were zero, then shut the Extract_Flag off. The update to true for any of the flags wasn’t the nasty part; rather, it got ugly when any of the flags were set to zero, which meant that all of the other flags needed to be checked to determine if the Extract_Flag needed to be set false.
To test this, you could issue the following statement, one for each ID:
UPDATE Config
SET Extract_Flag = A_val|B_val|C_val|E_val|F_val|G_val|H_val
FROM Config
WHERE ID=100
Here’s the easy way to do it! Run the following and see if you don’t like this one better than the above:
IF OBJECT_ID ('Config', 'U') IS NOT NULL
DROP TABLE Config
GO
CREATE TABLE Config
(ID int PRIMARY KEY NOT NULL
,Extract_Flag as A_val|B_val|C_val|E_val|F_val|G_val|H_val
,A_val bit DEFAULT 0
,B_val bit DEFAULT 0
,C_val bit DEFAULT 0
,D_val bit DEFAULT 0
,E_val bit DEFAULT 0
,F_val bit DEFAULT 0
,G_val bit DEFAULT 0
,H_val bit DEFAULT 0
)
GO
UPDATE Config
SET B_val = 1
WHERE ID=100
UPDATE Config
SET C_val = 1
WHERE ID=100
UPDATE Config
SET H_val = 1
WHERE ID=100
UPDATE Config
SET A_val = 1
WHERE ID=200
GO
SELECT * FROM Config
For more info, check out the links below.
Thanks for reading!
Lee
-----------------------------
I used a Neti Pot today – thought I would be the first to ever drown from one.
Links:
· Introduction to Bitmasking and bit manipulation
· Sweet search engine similar one formerly used at Match.com (includes c# project and all code/data)
