title: Optimize a string comparison by changing it to 5 int fields or 3 bigint fields?
body: <p>I am attempting to find a way to optimize a comparison between two SHA1 values in a SQL-Server 2008 R2 database. These are currently 40 character hexadecimal values stored as char(40) within the database. The values are indexed. The list of 'known values' is comprised of 21082054 unique entries. This will be used to compare against data sets that can range in size from under a dozen to billions of entries.</p>
  
  <p>As a software developer I understand that a 40 character string comparison is comparing 40 separate values, one at a time, with an early out option (As soon as they differ, the comparison ends). So the next logical step to change attempt to improve this would seem to be to move the hexadecimal value into containing integer values. This leaves me with 5 32-bit integers or 3 64-bit integers, int and long respectively for most languages these days.</p>
  
  <p>What I am not sure of is how well this line of thinking translates into the SQL-Server 2008 environment. Currently the SHA1 is the Primary Key of the table. To keep that same requirement on the data I would then have to make the primary key 5 or 3 separate fields, build an index on all of those fields and then replicate these changes from the known length table to the unknown length tables.</p>
  
  <p>TL;DR: Will changing a 40 character hexadecimal string into separate integer value fields increase comparison/lookup speed execution?</p>