I have tables with primary key uniqueidentifier type and add non sequential ids, now I want just to start adding only sequential ids in this tables. The guid generation is made in the code. Is it possible this to create problems in the indexes of the previous data. Logically I don’t see any problem, but I can’t find any information about cases like this.
P.S This is legacy project. I can’t update all the previous primary keys in the table to sequential, because there is no foreign key relations and mismatches will start occur in other tables.
Advertisement
Answer
First of all, I can give you an educated answer:
If the first sequential guid that you generate is greater than the last guid inserted in your table, you can be sure that further sequential guids you insert will not cause any indexing problems; since they are Sequential! To acheive this, You can do a work around and create the first SequentialGuid for each table in a way to be greater than the last inserted nonsequential guid in that table by passing the last inserted nonsequential guid to your Sequentialguid creator method. You can find a library in this link that allows you to create SequentialGuid with a lastId as base value.
Also, be advised that the order of sequentialguids can be modified in case a server reboot happens. So, SequentialGuids are not reliable from this perspective.
Finally, if it is applicable to your case, I would suggest you even a better solution:
- You can modify your tables by adding an Identity integer field as Clustered-index, and keep your Guid field as is BUT as a NonClustered-Index Primary Key Field.