Binary Data Type

I am trying to store a byte array in a database. I want to use binary to store the data but I am confused about the type. The byte array I am trying to store is a password hash from SHA512.

I hash a plain text value then store the result in a byte array. I then want to store the byte array in the database as binary but I am confused when its asking for the size of the binary field. In nvarchar a size of 2 would mean 2 characters.

How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?

The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.

I need to make sure the size of my binary field will hold the largest possible password hash, but I don't want it too large so its never completely used.

How is this data stored in the binary field, and what size binary field should I choose to make sure there are no problems with the password hash being truncated, yet making sure I'm not just wasting by creating a field thats too large.

Thanks!

The size is in bytes.|||

Use the varbinary datatype, since it expands to contain the content. That way you can overestimate the byte length without taking any (extra) space

passwordcolumn varbinary(2000) NOT NULL

/micke

|||

How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?

Ok, simple thing. What the size means, exactly, is written in the documentation - which you have and which you really should read. At one point. It is the MAXIMUM size of the data that can be stored. MAXIMUM - like, btw., with the nvarcher you mention as example.

The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.

You DO know the maximum size of the salt. The salt should always have the SAME size. Maybe you add spaces to it. Or "0". Or whatever. And then, you basically only need the maximum size anyway.

How is this data stored in the binary field,

Did I mention the documentation? There is a nice section in that explains exactly how SQL Server stores binary data. But frankly, you really do not need to know HOW it is stored. All you need to know for the problem in hand is THAT it is stored. The storage details are an implementation detail of the engine.

and what size binary field should I choose to make sure there are no problems with the password hash being truncated

Choose a size that is lare enough to store the hash. The hash will normally be static in size anyway. So, the size of the vield chould really be clear. That said I would NOT store it as binary. I would store it as char(x) and encode the data in a human readable way (i.e. for example as HEX). Makes debugging a lot easier.