SQL Server: String vs Binary?

Question

I have a load of records to store in a SQL Server 2008 database. Each of the records has a SHA-1 hash. Obviously storing the SHA in String form will take up 80 bytes compared to 20 if stored as Bytes.

When quering the database, which is SQL better at:

Comparing Strings?
Comparing Binary?

I need help deciding how to store the hashes as it has a huge storage impact on the database. Thanks for any help.

Thanks Marcelo - whould quering a record have better performance against Char than Binary? — Bram
– Bram, Commented Jul 5, 2010 at 0:38

Marcelo Cantos · Accepted Answer · 2010-07-05 00:53:52Z

3

Use BINARY(20). There are no performance issues (it should be faster, if anything). There are minor inconveniences with such values, such as the need to use byte[] instead of string in C#.

answered Jul 5, 2010 at 0:53

Marcelo Cantos

187k40 gold badges338 silver badges366 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cade Roux · Accepted Answer · 2010-07-05 02:02:17Z

2

HashBytes will return varbinary(8000), so I would definitely stick to binary - http://msdn.microsoft.com/en-us/library/ms174415.aspx - you'll be able to use it more easily in SQL Server

answered Jul 5, 2010 at 2:02

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

1 Comment

user174624 Over a year ago

Thanks for posting this. It's become the new thing I've learnt today.

Collectives™ on Stack Overflow

SQL Server: String vs Binary?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related