Re: Getting list of recently added IDENTITY items

Erland Sommarskog wrote:
David Portas (REMOVE_BEFORE_REPLYING_dportas@xxxxxxx) 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
--

.

Relevant Pages

  • Re: Getting list of recently added IDENTITY items
    ... num_of_hits for each hit within the same 3ms timeframe. ... The problem is fairly apparent here, because SQL Server has a fairly ... floating-point as a primary key. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... If I keep the primary key, ... >select packet_time,packet_data from packet where ... >have the clustered index (either on PACKET_TIME or on ...
    (microsoft.public.sqlserver.programming)
  • A pk is *both* a physical and a logical object.
    ... Primary key is a logical constraint. ... but SQL Server does not allow you to create a unique or primary ... constraints) by indexing is purely *platform dependent*. ... PKs are part of the ANSI SQL standard *for db implementations*. ...
    (comp.databases.theory)
  • Re: Re-Seed in SQL Server
    ... Add a new field to the table that holds the Primary Key. ... or something like that and make it an Autonumber field and a Primary Key. ... If the table is already on the SQL Server you could try the below. ... that your Autonumber field in the Access Database has gotten corrupted. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... Without having primary key in the table, will it slow down the query that I ... the clustered index on PACKET_TIME, and instead have a clustered index on ...
    (microsoft.public.sqlserver.programming)
Loading