Posted by David Portas on 03/13/06 11:53

Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> > So couldn't the (simplified) table look like this:
> >
> > CREATE TABLE hits (hit_datetime DATETIME ip_address VARCHAR(15),
> > num_of_hits INTEGER NOT NULL, PRIMARY KEY (hit_datetime, ip_address));
>
> While unlikely, it cannot be excluded that you have two hits from the
> same IP-address within 3 ms.
>
> This is a typical case where you need something like IDENTITY. Using
> a datetime value is just a gamble. Using a surrogate key which is
> assigned for each row is safe.

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.

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). Put another way: stating
the same fact twice doesn't convey any more information than stating it
once. This is one of the foundations of the relational model and its
basis in predicate logic.

--
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]