John W. Colby
jwcolby at colbyconsulting.com
Sun Sep 5 18:18:31 CDT 2004
OK, I can understand that logic. The time up front to build the index is outweighed in the time it takes to find an item based on the match key. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Sunday, September 05, 2004 6:47 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] SQL Server Hash algorithm - question, On 5 Sep 2004 at 10:44, John W. Colby wrote: > MATCH FIELD! That's the name they called it. What I haven't > discovered is whether the field is actually required or whether a > unique index on all these fields prevents dupes and I'm done. It > seems like an extra step to pull those X characters out, append them > all together, then drop them in a new field. They insist that it is > needed but they don't understand databases. I understand databases > but I don't know their business. > > I suspect that this new "match field" is then indexed to prevent > dupes. Is it used for anything else? Is it a standard definition > (how many characters from which fields)? > I doubt that there is a standard. "Match field" seems to be to just be another name for "natural key" (At least a qick scan of the some googles on this suggest so) If you create a field which contains a hash derived from x characters from a number of fields (where x varies according to the field , but is less than the maximum record length) and index that, it will make the index file smaller than indexing the fields directly and should be faster than a multi field index. "Match Field v Natural Key" looks to me like one of those arguments where the best solution depends on the type of data in use and the specifics of the db engine being used. -- Stuart _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com