However if the user needs to search for the PostalCode based on the Area, City & State information, the story is a bit different. The clustered index on the PostalCode and Area is sufficient for PostalCode based searches. (PostalCode, PostalArea, PostalCity, PostalState)
PRIMARY KEY CLUSTERED (PostalCode, PostalArea)
To demonstrate how hash-indexes may be of use in such a situation, consider the main data source table to be similar to the following ( Please NOTE: This example uses purely hypothetical data).
SQL CHECKSUM CODE
The main challenge that these applications face is that the search pattern may be very different depending upon the criteria used by the user - users can query either on the ZIP code (which can easily be indexed) or on the area address that consists at least of the City, State and Country. These are actual applications that can be found in India at the India Post ( ) or in the United States at the United States Postal Service ( ) web-site. A practical example for hash indexes using CHECKSUMĬonsider an application that allows users to search for a ZIP code based on an area address or vice versa. The reason CHECKSUM is important is that CHECKSUM fulfills all the requirements of a hash function: When applied over any two lists of expressions, CHECKSUM returns the same value if the corresponding elements of the lists have the same type and are equal when compared using the equal to (=) operator (considering that NULL for a given data type compare as equal).ĬHECKSUM therefore has a unique use in that it can be used to create hash-indexes on a table. That leads us to the question: Is CHECKSUM really required? If you observed CHECKSUM did not feature as a recommended solution for change detection and tamper protection. However, HASHBYTES may come at a storage overhead which is why the best "Value for money" solution is to use ROWVERSION for optimistic concurrency detection and BINARY_CHECKSUM for change detection.
The comparison that I came up with is shown below: A comparison of the various change detection and tamper protection mechanisms available in Microsoft SQL ServerĪs shown in the grid above, the best mechanism is the HASHBYTES function which works on the principle that the encrypted representation of the data is always different.
My articles on "An in-depth look at change detection in SQL Server" ( Part 01 and Part 02) attempt to compare each of these four methods. Microsoft SQL Server provides four (4) methods that may be used by teams for identification of modified records: