Advice Requested on Primary Key: Is char(20) better than binary(20)

I am not posting the DDL because it is not relevant to my question.
So far I have been unable to find a decent "natural key" for a table I am
designing. The true "natural key" is varbinary(MAX), which is unusable, and
so I have to consider a surrogate key, which is using SHA1 agorithm to
generate a 20 byte value of the natural key.
My Question:
Which would be the best choice for storing
20 bytes from SHA1 algorithm?
char(20)
binary(20)
I would like to choose the best datatype for Joins/Performance, etc. I am
using SQL Server 2005.
Thanks
Russell Mangel
Las Vegas, NVBINARY. The HashBytes function returns a VARBINARY value.
"Russell Mangel" <russell@.tymer.net> wrote in message
news:eUnwdJAkGHA.2004@.TK2MSFTNGP04.phx.gbl...
>I am not posting the DDL because it is not relevant to my question.
> So far I have been unable to find a decent "natural key" for a table I am
> designing. The true "natural key" is varbinary(MAX), which is unusable,
> and so I have to consider a surrogate key, which is using SHA1 agorithm to
> generate a 20 byte value of the natural key.
> My Question:
> Which would be the best choice for storing
> 20 bytes from SHA1 algorithm?
> char(20)
> binary(20)
> I would like to choose the best datatype for Joins/Performance, etc. I am
> using SQL Server 2005.
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>|||Russell Mangel wrote:
> I am not posting the DDL because it is not relevant to my question.
> So far I have been unable to find a decent "natural key" for a table I am
> designing. The true "natural key" is varbinary(MAX), which is unusable, an
d
> so I have to consider a surrogate key, which is using SHA1 agorithm to
> generate a 20 byte value of the natural key.
> My Question:
> Which would be the best choice for storing
> 20 bytes from SHA1 algorithm?
> char(20)
> binary(20)
> I would like to choose the best datatype for Joins/Performance, etc. I am
> using SQL Server 2005.
> Thanks
> Russell Mangel
> Las Vegas, NV
Your hash wouldn't usually be called a surrogate key because it's
derived from real, meaningful data. I'd prefer to call it a logical key
or business key but I think it's fair enough to call it a natural key
if you like.
Even without the hash, your problem wasn't that you couldn't find the
key but that you had no easy way to enforce it. It's an annoyance that
SQL Server won't permit unique constraints on "large" keys. I suspect
the reason is due to the fact that only B-tree indexes are supported
and that makes large keys impractical in Microsoft's way of thinking.

> Which would be the best choice for storing
> 20 bytes from SHA1 algorithm?
Use BINARY to avoid the overhead and complications of collations.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> My Question: Which would be the best choice for storing 20 bytes from SHA
1 algorithm?
char(20)
binary(20) <<
If you use CHAR(20), then you can display it easier, but that is the
only advantage I can think of. It would be nice to have a Teradata
style hashing routine that would give you a true surrogate key, hidden
from the users and with hash clash managed by the system. Sigh!|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150329186.339767.74520@.g10g2000cwb.googlegroups.com...
> char(20)
> binary(20) <<
> If you use CHAR(20), then you can display it easier, but that is the
> only advantage I can think of. It would be nice to have a Teradata
> style hashing routine that would give you a true surrogate key, hidden
> from the users and with hash clash managed by the system. Sigh!
Unless you convert the SHA1 hash to some character format, like Base64,
you'll run into problems displaying some hash codes that contain a 0x00 and
other special control characters in them. Base64, ASCII hexadecimal
representation, etc., of a 20-byte hash will require more than a CHAR(20) to
store.
-- Demonstration of CHAR(10) with an embedded 0x00 char
DECLARE @.x CHAR(10)
SELECT @.x = 'ABC' + CHAR(0) + 'DEF'
SELECT @.x
PRINT @.x