I have a table of events, clustered index is the creation date/time.
Normally there should never be two events happening in the same millisecond, but freak occurences happen, so the column is not neccessarily unique.
I want to add an uniquifier column, that is 0 for the first inserted row with a given time, 1 for the second, and so on:
TIME UNIQUIFIER, TEXT 2018-01-01 01:23:45.678, 0, "aaa" 2018-01-01 02:00:00.000, 0, "bbb" 2018-01-01 02:00:00.000, 1, "ccc" 2018-01-01 02:00:00.000, 2, "ddd" 2018-01-01 03:45:67.890, 0, "eee"
I know MS Sql Server creates such a column automatically, when I create the clustered index. However the column is hidden.
I would like a visible uniquifier, that behaves like this hidden one, so I can use it in foreign key relationships.
I.e. so that when ...
- another table needs to reference an existing event,
- and I only know the time when that event happened,
- and I only care about the first event that happened at the same time,
... I can use
Time:2018-01-01 02:00:00 / Uniquifier:0 as the foreign key, knowing this row exists, without me looking up the event in the first table.
I could use an autoincremented identity column instead to make the key unique, but this means I cannot reference an event without knowing its identity column.
For example if I want to create the event AND a bunch of additional event info in a separate 1-n table, I would have to first create the event, then look up its identity column, and only then create the related information, using that identity column. I am trying to avoid that roundtrip.
Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?
Or do I have to manually calculate such a column when I insert rows?