March 12, 2004 at 10:34 am
Hi,
I know big int uses 8 bytes for storage, but the range of numbers is 4 bytes for positive number and 4 bytes for negative numbers that give use a range from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
The question is:
I need to store an array of bits. I was trying to use bigint that gives me 64 bits but, I can't set all of the bits in 1 cause the number will go over the 2^63-1. Am I right?
The other problem I have is that sometimes (not all the time) I would need to store more than 64 bits, then the bigint data type won't work...
As far as i know the binary data type is used to store only files, so it won't work too.
I can use a varchar(13) datatype that allows me to store 104 bits. But I would prefer to use a number because I will have to compare this number with another one that is give me later on in the program. And for comparision purposes it would be better to compare numerics data type rather than varchars.
Does anybody can give me a suggestion on what should I do?
thanks.
Jose Luis
Kindest Regards,
@puy Inc
March 12, 2004 at 12:52 pm
I need to store an array of bits. I was trying to use bigint that gives me 64 bits but, I can't set all of the bits in 1 cause the number will go over the 2^63-1. Am I right?
No; the leftmost bit is used for sign, but it still works fine for bitwise operations.
As far as i know the binary data type is used to store only files, so it won't work too.
You're confusing binary with BLOB. But the only data types that work with the bitwise operators are the integer types.
Does anybody can give me a suggestion on what should I do?
My suggestion is to use first normal form, where your "array" would be represented by a table. I.e., instead of something like:
CREATE TABLE Objects(
ObjectID uniqueidentifier PRIMARY KEY,
ObjectName varchar(60),
ObjSettings bigint)
CREATE TABLE ObjSettings(
Position bigint PRIMARY KEY CHECK (Position IN (
POWER(CAST(-2 AS bigint),63), POWER(CAST(2 AS bigint),62), POWER(CAST(2 AS bigint),61),
POWER(CAST(2 AS bigint),60), POWER(CAST(2 AS bigint),59), POWER(CAST(2 AS bigint),58),
POWER(CAST(2 AS bigint),57), POWER(CAST(2 AS bigint),56), POWER(CAST(2 AS bigint),55),
POWER(CAST(2 AS bigint),54), POWER(CAST(2 AS bigint),53))), -- and so on
Descr varchar(60))
SELECT o.ObjectID, o.ObjectName, s.Descr
FROM Objects o JOIN ObjSettings s ON o.ObjSettings & s.Position = s.Position
You would instead use first normal form with a many-to-many relationship:
CREATE TABLE Objects(
ObjectID uniqueidentifier PRIMARY KEY,
ObjectName varchar(60))
CREATE TABLE ObjSettings(
SettingID tinyint PRIMARY KEY,
Descr varchar(60))
CREATE Table Objects_Settings(
ObjectID uniqueidentifier REFERENCES Objects,
SettingID tinyint REFERENCES ObjSettings,
PRIMARY KEY(ObjectID,SettingID))
SELECT o.ObjectID, o.ObjectName, s.Descr
FROM OBJECTS o JOIN Objects_Settings x ON o.ObjectID = x.ObjectID
JOIN ObjSettings s ON x.SettingID = s.SettingID
--Jonathan
March 12, 2004 at 1:57 pm
I think your solution is a little complicated to me and for the program I am tryint to develop.
Maybe I missed a point, I build the array of bits, but once I have done that, I don't need it to do bitwise operation over this number. Let me explain you better:
I have a program where the user can select a certain amount of numbers within the range from 01 to 20, commonly the amount of numbers goes from 1 to 10, but it can be up to 20 numbers within the range of 01 to 20 too.
Once those numbers are selected, another user enters the same quantity of numbers and I have to compare it with the ones entered before. It something like a lottery.
I planned to use 5 bits for every number 2^5 = 32, so the user can select numbers that goes from 01 to 32. Using a bigint the user can select up to 12 numbers (64 divided by 5), and sometimes the user can select more than 12 numbers (not happen very often but it can happen)
The actual program is using a varchar(100) where they store the number as follow:
1236547890-951736824-978645312-582963174
The problem with this solution is performance (the comparision of varchars is slower than integers) and scalability, if the user wants to select more than 9 numbers it can not be possible. That's why I thougt in using bits that can be converted to numbers and compare these numbers.
Hope I explain my self better and you can help me with this
Thanks,
@puy Inc
Kindest Regards,
@puy Inc
March 12, 2004 at 2:08 pm
Please explain how "up to 20 numbers within the range of 00 to 20" corresponds to a varchar(100) value of '1236547890-951736824-978645312-582963174'.
If you must "compare" the values "entered" by two "users," what are you comparing and how? Are you just interested in whether the complete values are equal or is there some more complex result from this "comparison?"
--Jonathan
March 12, 2004 at 2:23 pm
The way we are doing the things right now is using a varchar(100) and storing up to 9 numbers as follow:
'1236547890-951736824-978645312-582963174'
Thats why I want to find another way to do that, using smaller numbers (2 digit numbers instead of 10 digit numbers) for each user selection.
Let say the first user select
01,05,06,09,07
that will be
00001 00101 00110 01001 00111
that in decimal will be 1218855 which is the number i will put in the database
and another user select
01,05,06,09,04
that will be
00001 00101 00110 01001 00100
that in decimal will be 1218852 which doesn't match with the first number
Hope you understand me this time
Kindest Regards,
@puy Inc
March 12, 2004 at 3:04 pm
Unless obscurantism and complexity are priorities, this seems like a quixotic exercise. Why not just use a binary(20) so you can at least decompose the thing later. That would allow for up to 20 "numbers", each number being from zero to 255. You could create it easily by just concatenation:
DECALRE @b-2 binary(20)
SET @b-2 = CAST(250 AS binary(1)) + CAST(22 AS binary(1)) + CAST(231 AS binary(1)) + CAST(1 AS binary(1)) + ...
and then decompose it using SUBSTRING():
SELECT CAST(SUBSTRING(@b,1,1) AS tinyint), CAST(SUBSTRING(@b,2,1) AS tinyint), CAST(SUBSTRING(@b,3,1) AS tinyint), ...
--Jonathan
March 12, 2004 at 3:42 pm
well ... I little bit of obscurantism would be nice, cause this will be sensitive information 😀
But your solution is a lot better than mine, if you take a look at the first post I didn't understand very well how the binary data type works... now I am starting to do it (I hope so).
But now I have some other question
1. How this binary store the information and why
CAST(1 as binary(1)) + cast(2 as binary(1)) is not equal to
CAST(2 as binary(1)) + cast(1 as binary(1))
which in my case is nice because the position of the number is important.
2. How can I do this concat operation
SET @b-2 = CAST(250 AS binary(1)) + CAST(22 AS binary(1)) + CAST(231 AS binary(1)) + CAST(1 AS binary(10)) + ...
using let say delphi 6 or C# because the number will be calculated in the application not in the SQLServer, otherwise I would have to pass all the number the user select to the database. This second question I think will be answered knowing how this binary data type is stored internally.
Kindest Regards,
@puy Inc
March 12, 2004 at 3:44 pm
and another thing...
is there any problem using varbinary instead of binary?
Kindest Regards,
@puy Inc
March 12, 2004 at 4:00 pm
1. How this binary store the information and why
CAST(1 as binary(1)) + cast(2 as binary(1)) is not equal to
CAST(2 as binary(1)) + cast(1 as binary(1))
which in my case is nice because the position of the number is important.
Binary values are more akin to strings than numerics. That's why the plus sign is concatenation and not addition.
2. How can I do this concat operation
SET @b-2 = CAST(250 AS binary(1)) + CAST(22 AS binary(1)) + CAST(231 AS binary(1)) + CAST(1 AS binary(10)) + ...
using let say delphi 6 or C# because the number will be calculated in the application not in the SQLServer, otherwise I would have to pass all the number the user select to the database. This second question I think will be answered knowing how this binary data type is stored internally.
There's no magic here, although SQL Server binary values are difficult to deal with in some front-end languages like VB. This should be no problem with C, and I have no idea about Delphi. It's also easy (as in the above example) to pass the numbers in as integers and have a stored procedure or UDF convert them.
is there any problem using varbinary instead of binary?
You would need to compute the relative storage needs (as that seems of paramount importance to you). Varbinary uses an additional four bytes per value. For example, with one million rows, if your maximum number of values is 15 and the average number of values is 10, using varbinary(15) would save about one MB over using binary(15). If these values are static, you should not have issues with page-splitting if you use varbinary.
--Jonathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply