Check your counters

by Stéphane Faroult

All right, in the natural vs surrogate key religious war, I rather feel on the natural key side (I have nothing against using a surrogate key as a shorthand for a complex primary key, but I have seen too many people adding to a table a system-incremented column and calling it primary key without other analysis).
But I must admit that counters, whether you call them identity or auto-increment columns, or simply sequences, are extremely valuable for relating the techy's to the bean counter's vision of activity. In most applications, there is at least one such counter that represents fairly acurately the business activity - whether it is an increasing order number, invoice number, transaction identifier or whatever. Rather than execute complex queries with conditions on date columns to collect those so precious "key performance indicators", it may be easier to check the data dictionary, look for sequences or identity columns, and take a daily (or anything) snapshot of the current highest values, together with statistics about the number of executed statements and whatever your DBMS has to offer.


2006-03-12 07:44:36
Suppose the new release of an application increases the number of wasted keys--for instance, order numbers allocated for orders not completed due to failures of the application. How does your measure (number of order numbers allocated) relate to the the business result (number of orders completed)? How does it differentiate between that and a day of bad phone lines into a call center?

I'm also a little leery of running queries against the data dictionary--I've seen badly-written stuff clog block flows when the data dictionary was locked. How do you guard against this?

I'm not against what you're saying--I've used similar measures, and I bet you've got intelligent answers to those questions above--but it's good to spot the limits of a method.