Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Hash on database columns for quick equality compar

Author  Topic  dmenne Starting Member 5 Posts Posted - 2008-07-04 : 13:33:54 Dear Sql-Server-Friends, (I am new here) We are using SQL Server 2005 to manage questionnaire items. A typical table looks like SubID(primary) Item1 Item 2 .... Item100 ItemHash 11 1 3 2 4711 13 1 4 2 3141 14 1 3 2 4711 (This should be fixed font) Items are always in the range 1..5 or null. There are a few thousand rows per table, and we need a quick way to find those rows (=SubID) with the same item response. We thought of adding a trigger that updates an ItemHash (excluding SubID) computed from the columns. Since there are many tables and many items, it would be nice when the hash procedure where automatic, e.g. OnUpdate/Insert For each column in @tablename Append entry to a string (nulls could be coded as _) Create hash and update has column. I found the example in http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html quite helpful. Is this a good idea? Or are there easier ways to get this? Is it alternatively possible to use an index on the Items Column for quick hashing? Dieter [url][/url][url][/url] Dieter Menne Menne Biomed Consulting Tübingen