From: RD

To: Editor

 

I found this in sybase.public.ase.general.  I can't even bring myself to formulate a response.  Hope you find it entertaining.

 

"We are loading our tables daily without truncating them.  We do not want to end up accumulating duplicate rows and therefore decided to rely on IGNORE_DUP_KEY clause to silently reject dupes. But if a row is in fact different we'll preserve it because our original plan was to build a unique index on all the columns. But the following quote from the Transact SQL Manual looks like a showstopper to us:

 

"You can specify up to 31 columns in a single composite index in Adaptive Server 11.9.2 and later. All the columns in a composite index must be in the same table. The maximum allowable size of the combined index values is 600 bytes. That is, the sum of the lengths of the columns that make up the composite index cannot exceed 600.

 

Vast majority of our entities will have more than 31 attributes. Where are these limits coming from? Is there a way to bypass them (other than breaking a table into multiple pieces)? Is Sybase using some sort of hashing behind the scene to implement this feature? "

 

 

From: Fabian Pascal

 

I am not sure the average practitioner will see the problem here. How would you describe it?

 

From: RD

 

Well the main thing that strikes me is that there is no thought being given to what the real primary keys to these tables are. Never mind Sybase allows up to 31 columns in a composite index, there probably has to be some physical limit (and I'm sure they thought 31 was plenty).  Oracle probably has a different number as the limit, etc.

 

I'm thinking of this more from a modeling perspective. I've never even seen an example of a primary key with more than say five or six attributes.  Maybe I haven't been around long enough. But their "original plan" was to define every table's primary key to include all the columns in the table.  Any kind of blanket, one-size fits all approach like that smells of not knowing any better.

 

 

From: Fabian Pascal

 

Right. They seem completely unaware of what a key is and how to select it.

 

Had they read my chapter on keys, they would have realized there are four criteria for choosing a key and that any composite primary keys in general, let alone with 31 columns, are not desirable. They seem to have tables where the combination of all columns is the primary key, in which case they ought to use surrogate keys. But my bet is on poor design, which is rampant. This is a good example of what happens if you don't know fundamentals.

 

Aside: keys are implemented in SQL with indexes. This ties a logical feature to a specific physical implementation feature, violating data independence.

 

Posted 03/28/03

 

 

 

[ABOUT] [QUOTES] [LINKS]