MS SQL Database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUID
- by Eyvind
We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out GUIDs as PKs altogether, which we cannot do (at least not at this time)).
We could change the GUID generation algorithm to e.g. the one that NHibernate uses, as detailed in this post, or
we could, for the tables that are under the heaviest use, change to a different clustered index, e.g. an IDENTITY column, and keep the "random" GUIDs as PKs.
Is it possible to give any general recommendations in such a scenario?
The application in question has 500+ tables, the largest one presently at about 1,5 million rows, a few tables around 500 000 rows, and the rest significantly lower (most of them well below 10K).
Furthermore, the application is installed at several customer sites already, so we have to take any possible negative effects for existing customer into consideration.
Thanks!