Switching to sequential (comb) guides - what about existing data?

Question

We have a database with 500 + tables in which almost all tables have a clustered PC that has the guid (uniqueidentifier) ​​data type.

We are in the process of testing the switch from the "usual" "random" commands created by the .NETs Guid.NewGuid () method for sequential pointers generated using the NHibernate guid.comb algorithm . This seems to work well, but what about clients who already have millions of rows with "random" primary key values?

• Will they benefit from the new identifiers created from now on being consistent?
• Can there be anything to do with their existing data?

Thanks in advance for any pointers to this.

Answer 1

You can do it, but I'm not sure what you want. I do not see any benefit in using sequential directives; in fact, using directives is not recommended as a primary key unless the reasons for distribution / replication are related. Do you use a clustered index?

Having said that if you continue, I recommend that you first load the table with the values ​​from your algorithm.

You will have trouble with foreign keys. You will need to link the old and new roles in the above table, drop the foreign keys, perform a transactional update, and then reuse the foreign keys.

I don’t think it’s worth it if you don’t go away from directions to say that the system is based on integers.

Answer 2

It depends on whether the tables are grouped in the main index or another index. For example, if you create a large number of new records in a table with a GUID and a creation date, it usually makes sense to group by the creation date to optimize the insert operation.

On the other hand, depending on the requests made, the cluster in the GUID may be better, in which case the use of consecutive GUIDs can help with insert performance. I would say that it is impossible to give a definitive answer to your question without in-depth knowledge of usage.

Answer 3

I ran into a similar problem, I think it will be possible to update existing data by writing an application to update existing keys using the NHibernate guid.comb algorithm. To distribute new keys to the corresponding foreign key tables, perhaps it will be possible to temporarily cascade updates? Doing this through .NET code will be slower than a SQL script; another option would be to duplicate the guid.comb logic in SQL, but I'm not sure if this is possible.

If you decide to preserve the existing data, the use of the guid.comb algorithm should have some performance improvement, there will still be a pagination when inserting, but since the new tips are sequential and not completely random, this will be at least slightly reduced. the option to consider is to remove the clustered index of your primary GUID, although I'm not sure how much the existing query performance will affect.