Use decimal or varbinary data type?

[anilkmakhija]

I need to store 256 bit hash (SHA-2 alogrithmn) in one of the table's
primary key. I would prefer to use numeric data type rather varchar
etc.

* Decimal datatype range is -10^38 +1 to 10^38 -1. I can split my 256
bit hash into two decimal(38, 0) type columns as composite key
* I can store the hash as varbinary. I never used it and don't have
much understanding in terms of query writing complexities and dealing
it through ADO (data type etc.)

It would be heavy OLTP type of systems with hash based primary key
used in joins for data retrieval as well.

Please provide your expert comments on this.

Regards
Anil

[Erland Sommarskog]

(an**********@gmail.com) writes:

I need to store 256 bit hash (SHA-2 alogrithmn) in one of the table's
primary key. I would prefer to use numeric data type rather varchar
etc.

* Decimal datatype range is -10^38 +1 to 10^38 -1. I can split my 256
bit hash into two decimal(38, 0) type columns as composite key
* I can store the hash as varbinary. I never used it and don't have
much understanding in terms of query writing complexities and dealing
it through ADO (data type etc.)

It would be heavy OLTP type of systems with hash based primary key
used in joins for data retrieval as well.

I don't see what purpose the kludge with decimal should serve. Would you
not be left with a lot of code that converts forth and back?

Isn't the hash always always 256 bits? In such case would not binary(32)
be a better choice than varbinary.

Using a binary as key should be relatively straight forward. Of course,
there is some cost due to the size of the key, but that would not be any
better with decimal.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx