Использование CHECKSUM для создания хэш-индексов

CHECKSUM_AGG
This function returns the checksum of the values in a
group
CHECKSUM_AGG can detect changes in a table
CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )
1) ALL: Applies the aggregate function to all values. ALL is the
default argument.
2) DISTINCT: Specifies that CHECKSUM_AGG returns the checksum
of unique values.
3) Expression: An integer expression. CHECKSUM_AGG does not
allow use of aggregate functions or subqueries.

3.

CHECKSUM_AGG
• Returns the checksum of all expression values as int
• The CHECKSUM_AGG result does not depend on the
order of the rows in the table
• CHECKSUM_AGG ignores null values
• If an expression list value changes, the list checksum
value list will also probably change. However, a small
possibility exists that the calculated checksum will not
change

4.

CHECKSUM_AGG

5.

CHECKSUM
The CHECKSUM function returns the checksum value computed over
a table row, or over an expression list
Use CHECKSUM to build hash indexes
CHECKSUM ( * | expression [ ,...n ] )
*
This argument specifies that the checksum computation covers all table columns.
CHECKSUM returns an error if any column has a noncomparable data type.
Noncomparable data types include: cursor, image, ntext, text, XML
Expression
An expression of any type, except a noncomparable data type.

6.

CHECKSUM
Return types INT
CHECKSUM computes a hash value, called the checksum,
over its argument list. Use this hash value to build hash
indexes. A hash index will result if the CHECKSUM function
has column arguments, and an index is built over the
computed CHECKSUM value. This can be used for equality
searches over the columns.
CHECKSUM ignores the nchar and nvarchar dash character
(N'-' or nchar(45)). Therefore, a hash collision is
guaranteed for any two strings where the only differences
are dashes.

Использование CHECKSUM для создания хэш-индексов

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.