Posted by David Portas on 03/13/06 15:37

Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> > If you want to record more than 3ms precision then you'll have to use a
> > non-DATETIME datatype. IDENTITY won't help you do that. If you don't
> > need that level of precision then you can just increment the
> > num_of_hits for each hit within the same 3ms timeframe. Again, IDENTITY
> > doesn't help you. Either way my design is perfectly sound.
>
> No, it isn't. Using no_of_hits is a kludge, and also a performance problem,
> because instead of a plain insert, you need to do an IF EXISTS and then
> INSERT or UPDATE. Similarly, if you want count the number of hits, you
> need to sum no_of_hits, rather than doing count(*) which is likely to
> give the optimizer fewer choices for an effective query plan.
>
> The problem is fairly apparent here, because SQL Server has a fairly
> low resolution on time. But the problem is not the resolution, the
> problem is that time is a contiguous entity that does not have discreet
> values. Using time for as a primary key has the same problem as using a
> floating-point as a primary key.
>
> On the other hand, IDENTITY is a discrete set of value that is easy
> to work with. After all, that is all we know. This was hit 56762 that
> was registered. It happens to have the same values in the database as
> hit 56763, but we don't know if the values they model were the same or
> not. Thus, it would be incorrect to handle them as being the same. The
> only thing we can to discern them, is to add a number to the observations,
> so that we know that they are distinct.
>
> > Unfortunately these types examples just tend to become rather tedious
> > "what if this..." and "what if that..." exchanges. As a result they
> > aren't very informative. Fundamentally, my observation is simply this:
> > that there is no information in duplicate data that cannot also be
> > modelled in relational form (i.e. with keys).
>
> Yes, these exchanges are tedious, when someone insists on putting the cart
> before the horse, and tries to press a circular reality into a squared
> model.
>
> no_of_hits here is perfect example of this. It adds no information to
> the data, but only serves to make the data more difficult to work with.
> Certainly, it makes the relational purists sleep better at night, but
> that is usually not what the customer is paying for.

Now here is a more interesting slant on the key problem. When does a
metric become a kludge? I don't model account balances or stock as one
row per dollar or one row per pack - and I'm sure you don't either. We
use values in columns for those things. No_of_hits is surely a value
that is likely to be of interest to our users - in fact you've already
suggested aggregating the rows to get that value. So when does it
become "right" to put it in a column and "wrong" to create N rows
instead of just 1 row for the same data?

You seen to suggest that performance should be the deciding factor. My
view is that Normal Form is a better criteria. In any event, the
designer must make that choice and live with the consequences. In my
opinion it does no harm to point out yet again that the choice exists.
On the other hand it does a lot of harm to perpetuate the transparently
silly idea that "There isn't a natural key".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]