Storing Values for Bitwise &

  • I'm using a bitwise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.

    You also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?

  • nick.latocha (5/7/2015)


    I'm using a bitwise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.

    You also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?

    This topic comes up around pretty frequently. I have never liked the idea of this approach. It violates 1NF and makes it more difficult to query the data. I would prefer to use a number of bit columns. This is far easier to work with and doesn't take any extra storage space as the storage engine is smart enough to store up to 8 bit columns in a single byte. https://msdn.microsoft.com/en-us/library/ms177603.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks for your reply.

    The problem is the number of values can and will change - so my schema will also need to change each time a new value is added?

  • nick.latocha (5/7/2015)


    Thanks for your reply.

    The problem is the number of values can and will change - so my schema will also need to change each time a new value is added?

    That is certainly a different situation. If you are going to use bitwise logic how are you planning on handling that?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This was my prototype:

    create table #items (itemID varchar(10), statusID int)

    create table #statuses (statusName varchar(50), statusID int)

    insert into #items values ('Item 1',1|2|8)

    insert into #items values ('Item 2',1|4)

    select * from #items

    insert into #statuses values ('Status 1', 1)

    insert into #statuses values ('Status 2', 2)

    insert into #statuses values ('Status 3', 4)

    insert into #statuses values ('Status 4', 8)

    select * from #statuses

    select itemID, statusName from #items i

    Cross Join #statuses s

    where i.statusID & s.statusID <> 0

    drop table #items

    drop table #statuses

    The idea is that more statues can be added. But they will not be removed.

  • nick.latocha (5/7/2015)


    This was my prototype:

    create table #items (itemID varchar(10), statusID int)

    create table #statuses (statusName varchar(50), statusID int)

    insert into #items values ('Item 1',1|2|8)

    insert into #items values ('Item 2',1|4)

    select * from #items

    insert into #statuses values ('Status 1', 1)

    insert into #statuses values ('Status 2', 2)

    insert into #statuses values ('Status 3', 4)

    insert into #statuses values ('Status 4', 8)

    select * from #statuses

    select itemID, statusName from #items i

    Cross Join #statuses s

    where i.statusID & s.statusID <> 0

    drop table #items

    drop table #statuses

    The idea is that more statues can be added. But they will not be removed.

    Here is where this starts to become a serious pain. How do you find all rows that have the third status? This becomes increasingly difficult as the number of possible values increases. You have a very typical many to many relationship here. Don't make this harder on yourself than it needs to be. I would recommend creating a third table (ItemStatus) that contains the itemID and statusID.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You can use bigint, doubling the bits from an int ;-).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Sean Lange (5/7/2015)


    nick.latocha (5/7/2015)


    This was my prototype:

    create table #items (itemID varchar(10), statusID int)

    create table #statuses (statusName varchar(50), statusID int)

    insert into #items values ('Item 1',1|2|8)

    insert into #items values ('Item 2',1|4)

    select * from #items

    insert into #statuses values ('Status 1', 1)

    insert into #statuses values ('Status 2', 2)

    insert into #statuses values ('Status 3', 4)

    insert into #statuses values ('Status 4', 8)

    ...

    How do you find all rows that have the third status? This becomes increasingly difficult as the number of possible values increases.

    WHERE i.statusID & 4 <> 0. The number of values in this case doesn't really affect how to check for a specific one.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • That still only gives me 62 possible values? :hehe:

  • Yikes, how many possible "status"es are there??

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Well the example I gave was a dumbed down version of what I'm trying to achieve. For scalability I didn't want to be limited. I've started to build out the alternative third table idea Sean Lange suggested instead.

  • he could use a varchar 8000 or even a varchar(max) with text 0's and 1's! then he'd have an unlimited number of possible values!

    all he would need is a table with the mapping definitions(or a developer whos memorized each placeholders meaning...job security!)

    Add in delimitedsplit function, and it's all gravy.

    im sure that's much easier than individual columns or rows for each value pair:-D

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The fact is that bit-wise values can indeed be easier to maintain in certain cases, particularly for status and/or security flags. You can then add new values without having to change schemas and coding.

    That is not to say it's a recommended method for data in general, just that it has its place.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Store it as VARBINARY and "byte slice" it like they do with "Columns_Updated" in triggers. See the article at the following link, especially example "B". It won't be exactly the same because of the difference between the way you'd store the VARBINARY and the way "Columns_Updated" gets stored ("little Endian") but the principle will do you right.

    https://msdn.microsoft.com/en-us/library/ms186329.aspx

    I do agree that there should be a better way but I don't have enough details about your problem to say for sure.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for all the replies.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply