3

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?

  • 3
    Not without a lot of engineering. I'd probably just opt for an identity column. I realize you're looking for unique numbering for groups of duplicates, but it seems like overkill. – Erik Darling May 3 '18 at 11:58
  • Even if you manage to engineer it, I don't think it could behave exactly like the hidden one, because the hidden is only added when there (for rows) is needed. You want to use it as a target for FKs, so it has to be present in every row. – ypercubeᵀᴹ May 3 '18 at 12:16
  • I was afraid of that. I am not really trying to complicate this too much; i was hoping I could simply leverage the existing functionality of sql server, which does almost the right thing already. – HugoRune May 3 '18 at 13:08
  • "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" looks like you simply need SCOPE_IDENTITY() function (or OUTPUT clause) to get inserted identity value (or values). – i-one May 8 '18 at 16:01
7

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.

  • If you go for the transactional trigger, you will probably have eliminated the original problem as it will lag more than just some milliseconds :) – dlatikay May 3 '18 at 15:04
5

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.

  • An identity column will avoid dupes, and is of course an option. But it will make my foreign key reationship difficult. If I want to reference an event that happend at time X, I would have to first find that event in the table and look up its identity column, instead of assuming it has the unique key [timeX & 0]. I was hoping to avoid that roundtrip. – HugoRune May 3 '18 at 12:52
  • My question was perhaps a bit unclear on this point, I have tried to clarify why an auto-id is something I am trying to avoid. – HugoRune May 3 '18 at 13:06
  • I can think of no magic bullet for a foreign key that's essentially on time alone. A kludge could be a materialized flag for the row that would be assigned during insert to identify the first row and a different flag value for other rows. That would allow you to maintain a logical relationship but not with an actual foreign key constraint. – Dan Guzman May 3 '18 at 13:08
1

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 }).

  • @PaulWhite: ah, you are of course correct, I missed the +1 for when an existing record is found; fixed. For the event time, it sounds to me that the OP wants to be able record multiple events at the same time - that's why it's a parameter instead of a default constraint. It's "time the event happened" not "time the event was recorded." If that assumption is wrong, there are definitely simpler ways to handle it. – Jon of All Trades May 4 '18 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.