sql server - Ordered insertion at next unused index, generic SQL -

there have been various similar questions, either referred specific db or assumed unsorted data.

in case, sql should portable if possible. index column in question clustered pk containing timestamp.

the timestamp 99% of time larger inserted value. on rare occasions however, can smaller, or collide existing value.

i'm using code insert new values:

if not exists (select * foo timestamp = @ts) begin     insert foo ([timestamp]) values (@ts); end else begin     insert foo ([timestamp]) values (     (select max (t1.timestamp) - 1     foo t1     timestamp < @ts     , not exists (select * foo t2 t2.timestamp = t1.timestamp - 1))     ); end; 

if row unused yet, insert. else, find closest free row smaller value using exists check.

i novice when comes databases, i'm not sure if there better way. i'm open ideas make code simpler and/or faster (around 100-1000 insertions per second), or use different approach altogether.

edit thank comments ans answers far.

to explain nature of case: timestamp value ever used sort data, minor inconsistencies can neglected. there no fk relationships.

however, agree approach flawed, outweighing reasons use presented idea in first place. if understand correctly, simple way fix design have regular, autoincremented pk column in combination known (and renamed) timestamp column, clustered.

from performance pov, don't see how worse initial approach. simplifies code lot.

if can't guaranteed pk values unique, it's not pk candidate. if it's timestamp - i'm sure goldman sachs love if high-frequency trading programs cause collisions on insert , inserted 1 microsecond earlier because system fiddles timestamp of trade.

since can't guarantee uniqueness of timestamps, better choice use plain-jane auto-increment int/bigint column, takes care of collision problem, gives nice method of getting insertion order, , can still sort on timestamp field nice straight timeline if need be.