Sql-server – create a visible uniquifier column for a nonunique clustered index

computed-columnsql serversql server 2014unique-constraint

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?

Best Answer

Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?

No there is no built-in feature to do this.

Or do I have to manually calculate such a column when I insert rows?

You could consider using INSTEAD OF or AFTER triggers to maintain the correct uniqueifier-like behaviour for the same-time group when rows are inserted/updated/deleted.

This may require some careful thought to operate correctly under high concurrency, but it is probably best done as close to the data as possible (i.e. server-side, in the database, rather than client-side). A well-written trigger (with supportive indexing) will introduce minimal overhead.

Alternatively, make the index unique and implement retry logic. Since the expectations seems to be that collisions will be rare, this might well be acceptable.

Related Question