Using Binary Values to Store Database Information

Hello,

I was recently tasked with taking over a database that I have never seen before. The former developer loved to use binaries to store information in his database tables. It's really quite annoying, but it is what it is and I have absolutely no idea what I'm looking at here or how this works.

Here is an example below of a SQL statement:

SELECT feature_binary, feature_ID FROM table WHERE feature_binary & 2 = 2

Open in new window


Here is the result:
Binary query result
First off, how is the "feature_binary" (which is an INT in the table) derived from the feature_ID data? And two, what exactly is the code in the WHERE clause doing and how does it relate to values that have 258 or 514, for example.

Note, there is a "feature" table that feature_ID is (sort of) referencing, but the queries are using the "feature_binary" table instead.

Any help would greatly be appreciated.

Thanks!
infotechelgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It's bitwise logic. I guess that guy had either no idea about databases or he uses this in some C++/C# logic, where bitwise operators are available.

Without further information, you cannot really tell what is going on here.

We need to know, what these bit means.
0
Scott PletcherSenior DBACommented:
The big advantage of binary columns is that they can hold multiple bit entries in a single column.  Each bit can be tested and/or set separately.  Also, you can add new bit values without having to change the table schema by adding column(s), etc..

The values are based on powers of 2 (thus "binary" :-) ):
1, 2, 4, 8, 16, 32, 62, 128, 256, 512, 1024, 2048, 4096, etc..

With some practice, you can see that 258 = 2 + 256, and therefore 258 & 2 is "true" (non-zero).

If the bit being tested is off, the result will be 0.  Thus:
1 & 2 = 0

For certain types of things where space is critical, such as security settings, binary columns can be a very useful approach.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
infotechelgAuthor Commented:
Thanks, guys. Scott, that makes sense. I found this that may help me, too.

http://sqlfool.com/2009/02/bitwise-operations/

But, if I'm understanding what you're saying and what this article is saying correctly, these numbers are kind of useless to me because I don't really know what data values the original developer assigned to each bit value (because absolutely nothing was documented)?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
True.  Almost always in those cases a lookup table is built that describes what each bit value means.

If you don't have that, you'll have to build it up yourself over time by looking at code and by trial and error.
0
infotechelgAuthor Commented:
Thanks for your help on this.
0
ste5anSenior DeveloperCommented:
Just to make it clear: Those columns violate 1NF.
0
ste5anSenior DeveloperCommented:
Also, you can add new bit values without having to change the table schema by adding column(s), etc..

Sorry, @Scott, but this, well, is not correct. You also don't need to change a normalized model in this case. Just add a new row in the corresponding definition/lookup table.
0
Scott PletcherSenior DBACommented:
Sorry, but that's what I said there: I just said it awkwardly.  

"You CAN add new bit values WITHOUT having to change the table schema [WITHOUT] adding column(s) " [as you would have to do if not using the binary column technique]
0
ste5anSenior DeveloperCommented:
Maybe I'm too picky, but a normalized model could look like this:

USE tempdb;
GO

CREATE TABLE Features
    (
      FeatureID INT NOT NULL ,
      FeatureName VARCHAR(255) NOT NULL ,
      CONSTRAINT PK_Featues PRIMARY KEY ( FeatureID ) ,
      CONSTRAINT UQ_Feature_FeatureName UNIQUE ( FeatureName )
    );

CREATE TABLE Groups
    (
      GroupID INT NOT NULL ,
      GroupName VARCHAR(255) NOT NULL ,
      CONSTRAINT PK_Groups PRIMARY KEY ( GroupID ) ,
      CONSTRAINT UQ_Groups_GroupName UNIQUE ( GroupName )
    );

CREATE TABLE FeatureGroups
    (
      FeatureID INT NOT NULL ,
      GroupID INT NOT NULL ,
      CONSTRAINT PK_FeatureGroups PRIMARY KEY ( FeatureID, GroupID ) ,
      CONSTRAINT FK_FeatureGroups_FeatureID FOREIGN KEY ( FeatureID ) REFERENCES Features ( FeatureID ) ,
      CONSTRAINT FK_FeatureGroups_GroupID FOREIGN KEY ( GroupID ) REFERENCES Groups ( GroupID )
    );
GO

INSERT  INTO dbo.Features
        ( FeatureID, FeatureName )
VALUES  ( 1, '1' ),
        ( 8, '8' ),
        ( 9, '9' );

INSERT  INTO dbo.Groups
        ( GroupID, GroupName )
VALUES  ( 1, '1' ),
        ( 258, '258' ),
        ( 514, '514' );

INSERT  INTO dbo.FeatureGroups
        ( FeatureID, GroupID )
VALUES  ( 1, 1 ),
        ( 1, 258 ),
        ( 8, 258 ),
        ( 1, 514 ),
        ( 9, 514 );
GO

SELECT  FG.GroupID ,
        SUM(POWER(2, FG.FeatureID)) AS FeatureBinary
FROM    dbo.FeatureGroups FG
GROUP BY FG.GroupID;
GO

DROP TABLE dbo.FeatureGroups;
DROP TABLE dbo.Features;
DROP TABLE dbo.Groups;
GO

Open in new window


No need to change the model, when you want to add new features. And bitwise operation is non-sargeable.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.