• Jeff Moden (11/24/2012)


    opc.three (11/24/2012)


    However the OP is talking about producing their own function to generate sequential GUIDs that would not be affected by a restart. The tradeoff of course is that all GUID-generation would require a roundtrip to the database which has its own set of baggage.

    Considering that the OP has also identified that the existing GUIDs have been used in "thousands of lines of code", I'm hoping that he won't consider such a thing. It would even break GUI code unless they write a wad of "Instead Of" triggers to covert the UUIDs the GUI code is already passing.

    I think I disagree, Jeff.

    If id generation is one place, at most three triggers are needed; that's pehaps a bit less than a wad of triggers. Of course there's also a view (since there's no reasonable way to do partitioning between new GUIs and old GUIs within one table, it has to be two separate tables) and that's what is operated on. Maybe fewer than three triggers are needed (I can't remember how good SQL Server is at doing updates and deletions on a union view without trigers - certainly updates and deletions on unions have been done in other dbs without needing a trigger, so maybe only the insert trigger to trap duplicate keys is needed; and in fact a try catch could be used instead of that trigger if the insert statement used the new table instead of the union view, but I doubt that would be much cheaper in development, testing, or performance than inserting into the view and using the trigger to detect duplicates and direct to the new table). The overhead of the insertion trigger is pretty small too: just a check whether the generated value is already in the old-keys table, and if it is regenerate (doesn't need a new GUID, just adding one to the sequential parameter of the generator) and check again, and the check will hit a match only about one time in ten to the power thirty three even if there are as many as a billion rows in the old table, so the performance overhead is small).

    I could be wrong here - I've never done this trick with GUIDs.

    Tom