best way to keep track of an ordered smalldatetime column

Question

I know the difference between smallDateTime and timeStamp. Right now I am using smallDateTime because the precision is good enough for my needs. But the problem is that I still need to keep inserted items in order and smallDateTime is mixing things up if the inserts come too close to each other.

Is it an acceptable practice to use both a smallDateTime and a timeStamp field? I would use the smallDateTime to know when the item was inserted and the timeStamp field to order things chronologically.

Note: I do not consider using DateTime a valid solution since it will have the same problem with keeping things ordered if the inserts are made fast enough.

Answer 1

How about using an autogenerated identity field as the primary key? Using synthetic keys is, I think, a pretty good idea, coupled with a unique index on the natural key. This would solve your ordering problem as the records would be ordered by the primary key.