Post reply

Storing Values for Bitwise &

  • May 7, 2015 at 7:59 am

    #398422

    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?

  • May 7, 2015 at 8:10 am

    #1796233

    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/

  • May 7, 2015 at 8:17 am

    #1796238

    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?

  • May 7, 2015 at 8:31 am

    #1796248

    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/

  • May 7, 2015 at 8:42 am

    #1796260

    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.

  • May 7, 2015 at 8:49 am

    #1796265

    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/

  • May 8, 2015 at 9:53 am

    #1796632

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

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 8, 2015 at 9:56 am

    #1796634

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 8, 2015 at 9:59 am

    #1796637

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

  • May 8, 2015 at 10:02 am

    #1796639

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

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 8, 2015 at 10:04 am

    #1796641

    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.

  • May 8, 2015 at 3:34 pm

    #1796741

    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!

  • May 8, 2015 at 3:51 pm

    #1796749

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 9, 2015 at 9:43 am

    #1796811

    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)
    Intro to Tally Tables and Functions

  • May 11, 2015 at 1:02 am

    #1796879

    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