Archives of the TeradataForum

Message Posted: Thu, 19 Sep 2003 @ 02:10:17 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

No, but there are good reasons to use them - principally to preserve the customer known key, unify different domains but avoid the complexity, performance impact and loss of clarity from using surrogates.

I have used varchar for primary indexes for almost every base table at a customer site and have never encountered any real issue

I use it to unify keys coming from different sources - apply a standard formatting to the source keys from each source and prefix them with a source system code of say 2-3 characters and usually prefix further with another character to indicate whether the source system is internal or external.

In the site that I did it the kernel tables (acct, cust, policy, package etc) had around 20m rows with 5 rows per key (and PI) value owing to history. The transaction table had over 300m rows with avg 100 rows per acct, but with many in the multiple thousands up toa few with 40,000 rows for the one value The Transaction table was defined as Multiset to stop Multiload doing the duplicate row check but all the others are Set tables.

The max varchar used is varchar(30) but the maximum currently in use is 18-20 characters (some accts are 18, some 15 etc)

I have heard second hand that some people think the hashing algorithm takes longer to produce the hash on a varchar column compared to a numeric but I think it practice the penalty is tiny (ie infinitesima) compared to the savings from not having to employ / join and manage surrogate keys (ie. translating the source identifier into an arbitrary number)

eg. my index values SDDA001234567890123 from source DDA which has 15 digit acct numbers currently only using 13 , SLLC123456789 from source LLC which has 9 digit numbers and currently using the full width in the source system and so on for , in my case, 10 differnt source systems.

Summary - use them!!

	<Prev	Next>		<<First	<Prev	Next>	Last>>