Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have come across what looks to me like a slightly odd pattern in a SQL Server 2005 database I'm taking care of, and was wondering whether it's just me, or whether it really is odd.

There are a number of tables with uniqueidentifier primary keys, which also have a computed column which is the CHECKSUM of that key, e.g.

[CustomerGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[CustomerHash]  AS (CHECKSUM([CustomerGuid])) PERSISTED,

Then, there are indexes which contain both of these fields, e.g.

CREATE NONCLUSTERED INDEX [IX_Customer_CustomerHashAndGuid] ON [dbo].[Customer] 
(
    [CustomerHash] ASC,
    [CustomerGuid] ASC
)

This pattern also pops up with Guids that are not primary keys - e.g., an Order table with CustomerGuid and CustomerHash for each order, and an index on those two columns for looking up orders by customer.

Surely, the whole point of a checksum is that you create an index just on the checksum, so a SELECT will retrieve the records that match the checksum, and then compare the underlying value as a safety check? Doesn't putting the underlying value in the index waste a bunch of space for no real gain?

share|improve this question
    
Are those checksums also used by some layer above the database perhaps? –  Jack Douglas Jul 11 '11 at 9:22

3 Answers 3

A company that I worked at did something similar where they hashed the strings in the table using checksum and there are all sets of duplicates that we started getting. We ended up having to switch to MD5 which was better, but still not perfect.

In this case I wouldn't bother with it. GUIDs are only 8 bytes, INT is 4. You aren't saving that much space, and the CHECKSUM values will be just as random as the GUIDs are.

share|improve this answer

Hash/Checksum should be constant for a given GUID or Customer#. They won't aid in uniqueness (unless broken). It will slow down unique queries using them as they will need to be calculated before being used in the query. Hash and Checksum should be derivable from the corresponding key, and thus really don't belong in a normalized database.

I would index the key values without the derived values.

Before removing the hash and checksum from the database, check to see that code is not referencing them.

There may be high volume cases where the both the id and hash/checksum are entered and the CPU cost of calculating the checksum is high enough that having the derived values in the database is useful.

share|improve this answer

You are right, this is pointless.

Two (of many) reasons that I see it's wrong

  • it isn't guaranteed unique (CHECKSUM gives int) whereas the GUID is (over the range of GUID). It's a small chance of duplicate but quite possible: like the "birthday problem" somewhat

  • it's still random order. The main reason IDENTITY is better then GUID for a clustered index is that IDENTITY is monotonically increasing. CHECKSUM(someGUID) is random order too

I'd add a new IDENTITY column, and then start changing dependencies to use this only.

share|improve this answer
    
I don't think the uniqueness is an issue (in terms of collisions returning incorrect data), since queries are of the form WHERE CustomerHash=208965900 AND CustomerGuid='353F94B7-1084-45A4-BF19-2E509C0A1229', it's the efficiency I'm concerned about. –  Carson63000 Jul 11 '11 at 7:17
    
@Carson: is the hash used anywhere else or only in these joins? Because if so.. I think they are a bit pointless. –  Marian Jul 11 '11 at 7:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.