UniqueIdentifier Update on a legacy SQL Server database

Longstreet, James [USA] 1 Reputation point
2022-08-12T12:57:45.467+00:00

A database that was originally written with many foreign keys, many of them clustered keys, has been upgraded to use uniqueidentifiers for all the tables as their primary key. There are solutions out there (see link below) that discuss the concept and the initial logic when the new database was developed was to use the entity framework to manage the tables going forward with a code-first approach. The relationships in the legacy database are highly dependent upon the FK structure.

The main problem with this approach is the previously clustered foreign keys will become "un-clustered" and performance will suffer.

I have some options on the table to pursue:

  • Move the data to a memory optimized database (not really an option - db is moving to an 8 GB / 4 vCPU solution)
  • Add new reference tables that contain the original clustered FK's and their GUID equivalent
  • Retain all foreign keys as clustered indexes and provide the uniqueidentifiers associated with those keys as the actual clusters
  • **** These clusters reference integers from another table and the foreign key relationship is retained thus the pairing of GUIDs could conceivably rely upon the lookup from these clustered indexes.

I remain open to the concept of uniqueidentifiers for primary keys but the upgrade from an older database where the clustered index is part of the original performance concept is throwing me off. There is a good white paper on the use of uniqueidentifiers [https://azure.microsoft.com/en-us/blog/uniqueidentifier-and-clustered-indexes/]

I'm struggling mostly with where a foreign key is now both an integer and a uniqueidentifier and that this is a square peg that must fit in a round hole.

What would be most helpful is a model database of this sort not unlike the ones Microsoft offers with SQL Server, like Northwind or the Publishing database, that provides some insight into how entity framework accesses these keys and how the foreign key associations are built into MVC code-first. I've build a pseudo in-mem sample of the database in question and plan to add GUID primary keys to replace the ones there as a test. But the weight of the project is growing and a more feasible solution from the forum would be helpful. Since this sort of question is very unpopular, I am open even to hearing why I shouldn't do this. Hearing why I shouldn't helps me to build my resolve as to why I should.

It would also be helpful to hear from some of the developers who have dealt with upgrading a Sql Server database to fit a code-first model and the sort of difficulties they face and how these were overcome.

Sample table using link above 

CREATE TABLE [dbo].[myTable](  
  
	[Id] [uniqueidentifier] NOT NULL  DEFAULT (newid()),  
	[date] [datetime] NOT NULL DEFAULT (getdate()),  
	myval nvarchar(50) NULL,  
  
PRIMARY KEY CLUSTERED (	[date] ASC)  
)   
  
insert into mytable(myval) SELECT '245thusu'
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,347 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,247 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-08-12T16:15:43.753+00:00

    First, using uniqueidentifier as a PK is not appropriate unless you need to merge data across servers. An IDENTITY field works perfectly fine unless you need a unique key across servers.

    Second, a PK does not need to be the clustered index, and uniqueidentifer should never be a clustered index for many reasons.

    The simplest answer, if you don't need unique keys across servers if to change them back to INT IDENTITY fields.

    See:
    https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/data-annotations#key

  2. Bruce (SqlWork.com) 55,196 Reputation points
    2022-08-12T18:26:36.9+00:00

    First your clustered index does not need to be the primary key. You should make you clustered index the natural order for each database?

    In fact guids are a poor choice for a clustered index (lots of bucket splits and no nature order). You can also create covered indexes which work a clustered tables.

    One of the main advantages of guids as primary key, is they can be safely generated before storing. This makes parent / child relations much easier to save.

    The main downside of guids over integers is the size and random nature, but there are new sequential guid generators helpful for the database primary keys. In fact EF has one

    https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.valuegeneration.sequentialguidvaluegenerator?view=efcore-6.0

    0 comments