create a visible uniquifier column for a nonunique clustered index

Question

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?

Answer 1

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.

Answer 2

Adding an identity column to the CI key will avoid dups similarly to the internal uniqueifier value. Be sure to choose a data type like bigint to avoid overflow.

If the source data contains greater time precision than you currently store, consider using datetime2(7) to reduce collisions and provide more accuracy.

When you only care about the first event for a given time, use ROW_NUMBER() OVER(PARTITION BY time ORDER BY time, identity_column) and select row number 1.

Answer 3

I would go with Dan Guzman's solution of an ordinary INT IDENTITY (or BIGINT); it's simple, and will have minimal overhead.

If you really want to store ordinal values for each unique date/time value, perhaps so you can index and identify such dupes for later correction, you could do so in your write sproc:

CREATE PROC WriteEvent(@EventTime DATETIMEOFFSET(7), @EventText VARCHAR(100))
AS BEGIN
    BEGIN TRAN
    BEGIN TRY
        DECLARE @ExistingUniquifier TINYINT =
            (SELECT MAX(Uniquifier) FROM EventsTable WHERE EventTime = @EventTime)

        INSERT INTO EventsTable (EventTime, Uniquifier, EventText)
            SELECT @EventTime, COALESCE(@ExistingUniquifier + 1, 1), @EventText

        COMMIT
    END TRY
    BEGIN CATCH
        -- Log the error
    END CATCH
END
GO

However, you will need to implement some retry logic in the CATCH block. As written, if two writes run simultaneously only the first will succeed. I.e., both sprocs start; both get "NULL" as the existing uniquifier, both try to write with a uniquifier of 1 and only one succeeds (assuming there's a PK or UQ on { EventTime, Uniquifier }).