Usage of SQL Server Hash Functions and precautions

Hash is used in many system designs, including OS and SQL Server, to greatly improve the system performance. In fact, we can also use the hash function of SQL Server to achieve the same effect. For example, you can create a hash index for text with a long string to quickly improve the query capability and compare table data differences.

There are two examples below:

1. used to compare the data similarities and differences between the two tables.

For example, you can find records that exist in Table T1 and not in table t.

Select *

Fromt1

Wherechecksum (*)

Notin (selectchecksum (*) fromt)

2. Used to create a computed column Index

The following example shows how to use checksum to generate a hash index. Add the calculated checksum column to the index table, and then generate an index for the checksum column to generate a hash index.

-Create
Checksum index.

Set arithaborton;

Useadventureworks;

Go

Alter table production. Product

Add cs_pname aschecksum (name );

Go

Create index pname_indexonproduction.product (cs_pname );

Go

Checksum indexes can be used as hash indexes, especially when the columns to be indexed are long character columns, the indexing speed can be improved. Checksum indexes can be used for equivalent searches.

/* Use the index in a SELECT query. Add a second search

Condition tocatch stray cases where checksums match,

But the valuesare not the same .*/

Select *

From production. Product

Where checksum (n'bearing ball') =
Cs_pname

And name = n' bearing ball ';

Go

 

Creating an index for a calculated column is embodied in a checksum column. Any changes made to the productname value are propagated to the checksum column. You can also directly generate an index for the index column. However, if the key value is long, it is likely that the checksum and index are not executed, or even the regular index.

However, using checksum () is also risky. Two different values may generate the same hash value. In this way, the data J will be wrong, although there are few such cases.

For example, the following two data types are different, but the hash results are the same.

Declare @ guid1uniqueidentifier, @ guid2uniqueidentifier

Select @ guid1 = '3db7d309-A8F4-47C4-BA90-0CB458B44CB0 ', @ guid2 = 'efe7f2c5-19f9-42b9-9c16-21bed41e882b'
Select chksum_guid1 = checksum (@ guid1), chksum_guid2 = checksum (@ guid2)

Some experts have provided the following solutions to the problems mentioned above: You can use checksum twice to perform reverse hash at one time and then add the two structures together, in this way, there are very few opportunities for collision.

Declare @ guid1uniqueidentifier, @ guid2uniqueidentifier

Select @ guid1 = '3db7d309-A8F4-47C4-BA90-0CB458B44CB0 ', @ guid2 = 'efe7f2c5-19f9-42b9-9c16-21bed41e882b'

Select chksum_guid1
= Convert (bigint, convert (binary (4), checksum (reverse (@ guid1) + convert (binary (4), checksum (@ guid1 )))
, Chksum_guid2 =
Convert (bigint, convert (binary (4), checksum (reverse (@ guid2) + convert (binary (4), checksum (@ guid2 )))

After optimization, we can see that the hash structure is different, so as to avoid the collision of hash values.