SQL Server Performance

Check if record exists with nvarchar comparison

Discussion in 'General Developer Questions' started by Roberto, Mar 6, 2009.

  1. Roberto New Member

    I have a table with about 1 million rows, and i need to check if a record exists with a nvarchar value that is greater than 1000 bytes.
    I can't make indexes over more than 450 bytes column (nvarchar) so this is getting very slow (about 0,5 seconds).
    I tried to use CHECKSUM, but i get 4000 rows with the same checksum and different values.
    Maybee with a user defined function and some algorithm i get the value in a 256byte column (with unique values), just to check if the record exists?
    I haven't found anything like that...
    Thanks for help :)
  2. moh_hassan20 New Member

    For sql server 2000, you have to create extended stored procedure compiled using one of language such as vb, vb.net or c++ for one of the standard hashing algorithm: MD2 , MD4 , MD5 , SHA , SHA1 which are ready available in dotnet namespace without re-writing the algorithm.

    the generated hash string length is:
    MD2, MD4, and MD5: 128 bits (16 bytes)
    SHA and SHA1: 160 bits (20 bytes)

    in sqlserver 2005, and sql2008, it is available in t-sql function hashbytes
    example in sql 2005:
    Select HashBytes('MD2', 'hash any text up to 8000 char') ---> generate 16 byte
    Select HashBytes('SHA1', 'hash any text up to 8000 char') ---> generate 20byte
  3. Roberto New Member

    exactly what i looked for :)
    Thank you very much for the fast answer!
  4. Roberto New Member

    Just one more question,
    i read the md5 or sha-1 hash strings are not collision resistent and there could be 2 same outputs?
    I really need the values to be unique, so maybee SHA256 with 256 bytes provide less chance to get the same values?
  5. moh_hassan20 New Member

    SHA-256 is collision resistant : 256 bits /8 = 32 byte and stored in sql server as hexadecimal 32*2 = 64 char
    and sure it is ideal , and your index key is of length 64 char.
    SHA-1 is collision resistent with minor flaw which is very very rare to collide
    it means that the chance of collision between hash values is much reduced and it is enough
    MD1-5 are not collision resistant (so you can avoid them)
  6. Roberto New Member

    ok i have to do this with a asp.net script at the moment, in future we are going to change to sql 2008.
    Thanks again for your answer :)
    Have a nice weekend,

Share This Page