DataType for bit Comparison

I need to store a string of 16 bits in two different tables and do bit operations on the values.  For example, a field in one table might be the value "0101010101010101" and a field in the other table might be "0001100111100010".  In VBA, I'll need to XNOR them together, get the result, and "OR" the result with another string of 16 bits.  What's the best data type to use to store these values?
LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
For SQL server, use an Integer field.
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You may have to use a char(16), as numeric fields like int will strip off leading zeros.
SQL Server does not have a data type specifically suited for a string of bits.

OR

Add up their bit values and store the sum as an int.   For example, 0000000000001011 = 8 + 2 + 1 = 11.  Then use one of the SQL Server Bitwise Operators to write expressions for the number.   I had one client that did complex health care management that did this for various illnesses, 1 = Rheumatoid Arthritis, 2 = Hypertension, 4 = Diabetes, you get the idea, where some patients would add up to 1,8376,432.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
agree with Anders ... data type will not have to be converted to do bitwise math plus Integer takes only 2 bytes (16 bits - just what is needed) to store as opposed to 1+ byte/character for string and then string would have to be cast to a number for bit-math
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
An int might not work, as inserting 0001100111100010 into an int will strip the leading zeros and only save 1100111100010 (without the first three zeros).  

Unless there's some kind of conversion function to add the leading 0's back if that is necessary expressions.   If it's not necessary for expressions then int works.

And yes saving it as 4 bits is always better then saving it as 17 bits.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
adding on

bit <> byte

bit = 0 or 1
byte is a "word" or set number of bits -- for example, 8 bits such as 10001010
 
1 byte can often be equated to 8 bits, but that is not always true; it depends on the environment. Byte can be more bits.
~~~
getting back to original question:

since math will be used on data, best to store as a number. Dropping initial zeros for displaying has no impact on numbers that are stored.
0
Gustav BrockCIOCommented:
You answered your own question:

I need to store a string of 16 bits

So text of length 16.

/gustav
0
Declan_BasileITAuthor Commented:
Is there any way that I could change this SQL Server code to be a loop?

ALTER TRIGGER [dbo].[trigUpdateBitValues] 
ON [dbo].[CodeTrackingReqs] FOR INSERT, UPDATE
AS

	DECLARE @CumulativeSame As Integer
	DECLARE @CumulativeDontCare As Integer
	DECLARE @CodeTrackingReqId As Integer
	DECLARE @Attrib As TinyInt

	SET @CodeTrackingReqId = (SELECT Inserted.CodeTrackingReqId FROM INSERTED)
	SET @CumulativeSame = 0
	SET @CumulativeDontCare = 0

	SET @Attrib = (SELECT Inserted.Attrib1 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,0)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,0)
	
	SET @Attrib = (SELECT Inserted.Attrib2 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,1)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,1)

	SET @Attrib = (SELECT Inserted.Attrib3 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,2)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,2)

	SET @Attrib = (SELECT Inserted.Attrib4 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,3)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,3)

	UPDATE CodeTrackingReqs SET SameMask = @CumulativeSame, DontCareMask = @CumulativeDontCare
		 WHERE CodeTrackingReqId = @CodeTrackingReqId

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Is there any way that I could change this SQL Server code to be a loop?
I recommend asking that as a new EE question, and responding to the experts that spent time and effort responding to your original question.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "So text of length 16"

not necessarily. Integer stores 16 bits in Access -- should be good plus it is already a number data type.
0
Declan_BasileITAuthor Commented:
Thank you everyone for your feedback.  I think integer will work best because I need to do bit operations but don't need to graphically represent the number in binary.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
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.