May 7, 2015 at 7:59 am
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
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
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
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
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
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
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".
May 8, 2015 at 9:56 am
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".
May 8, 2015 at 9:59 am
That still only gives me 62 possible values? :hehe:
May 8, 2015 at 10:02 am
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".
May 8, 2015 at 10:04 am
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
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
May 8, 2015 at 3:51 pm
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".
May 9, 2015 at 9:43 am
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
Change is inevitable... Change for the better is not.
May 11, 2015 at 1:02 am
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