Add API to correctly insert GuidV7 to SQLServer (BigEndian), so no index fragmentation occurs

[jogibear9988]

Describe the bug

If GuidsV7 are added to NET9, SQLServer inserts them in a way, so the index still gets fragmented (see issue: dotnet/runtime#103658)

There should be a new setting in the SQLClient to switch GUID serialization to "TryWriteBytes(destination, bigEndian: true)" so also SQLServer can profit from the advantages of the new guid format.

[jogibear9988]

see also: dotnet/runtime#103658 (comment)
with a comment from @tannergooding

    This would then be something to raise with the SQL Server team, which is fully external to the .NET Libraries team and has their own management, customers (including beyond .NET), developers, back-compat bars, API review process, and other considerations.

[jogibear9988]

https://feedback.azure.com/d365community/idea/69d13f8a-b9fb-ed11-a81c-000d3adb7ffd
https://feedback.azure.com/d365community/idea/cb67ad60-2433-ef11-a296-6045bdbc5330

[MichelZ]

#2946 is a prereq for this

[edwardneal]

It's also worth referencing dotnet/efcore#33579, which covers a similar problem within EF Core.

[jogibear9988]

It's also worth referencing dotnet/efcore#33579, which covers a similar problem within EF Core.

I thought maybe add a Parameter to ConnectionString, like "SerializeGuidsBigEndian:true" could work. So also EFCore or any other ORM could benefit.

[ErikEJ]

@jogibear9988 Should it not be possible to simply read the new Version property? If so, a much better UX I think.

[jogibear9988]

@jogibear9988 Should it not be possible to simply read the new Version property? If so, a much better UX I think.

??? Wich Version?
You could also create a GUID V7 before NET9, and stor it already in the Database. If you now change the byte order, you maybe break old Programs. I would create a setting, and the read/and write all GUID's as BigEndian.

[Wraith2]

If the setting to change how guids are handled is in the connection string then you can't migrate a database from using one type to another without losing the ability to use guids that already exist and that doesn't seem like an acceptable change to me.

Can you provide some simple tests for 1) "normal" guids and 2) v7 guids that show how they should be written and read back? I suspect that we need to have dedicated read and write methods because the type does not contain information about whether the bytes should be interpreted in a specific way or not. Consider how you would want to have both old and new guids used as parameter values. How do you specify that the contents are one or the other?

If we need to extend only Sql* types like SqlDataReader with a method like GetGuidV7 then that's ok but if we need to push that up to DbDataReader then that requires a framework change and I don't know what the opinion of other providers (postgres, mysql, sqlite) would be about that change. /cc @roji

[jogibear9988]

Second thought about my suggestion, it will not work. It will, but then the guids will have another value when written with dotnet as when you read them without the connection parameter. I think the only correct solution would be a new Guid Type in Sqlserver, that serializes the same as all other databases do.

[edwardneal]

Now that I've got a bit of time to respond properly!

I think you're right; a blanket change to the way GUIDs are written to the network wouldn't work. It'd mean that every primary key value would be interpreted differently, and potentially that a developer could run a query from SQL Server Management Studio/Azure Data Studio and see a GUID with one value, but run the same query via M.D.S and see another value.

The real problem isn't the way that a TDS library transports GUIDs to the server, it's the way that a client generates GUIDs in the first place. The EF Core issue I linked is designed to address that for one library.

A better approach may be to create a utility method similar to SqlGuid.NewGuid(), which wraps Guid.NewGuid() and makes the result sortable by SQL Server. I'm not sure where this'd need to go to though. If that's a practical addition to something in the SqlTypes namespace, it'd be an API request in dotnet/runtime. If the SqlTypes namespace isn't accepting changes, maybe we could have a SqlGuidExtensions utility class in our SqlTypes namespace.

[Wraith2]

The real problem isn't the way that a TDS library transports GUIDs to the server, it's the way that a client generates GUIDs in the first place.

Guids are just data. SqlClient doesn't generate them at all. We're currently in a situation where anything that is identified as a guid gets handled in a specific way and that works. What we have to add is some way to have a second handler without having a second type and that means the type information must be maintained out of band by the user.

The SqlParameter input case is mentioned is indicative of this. If you just put a guid into an object typed variable then there isn't enough information. The same problem happens in the other direction. If you have some rowdata which is typed as uniqueidentifier by sql server there is no way to know if that's old or new format. So whatever happens I don't see any other way to proceed than having special methods to both read and write the new format. We'll also need a wrapper type for object typed location like SqlParameter.

[vanbukin]

Depending on what type of data is used in the database column for storage, indexes may or may not be fragmented (uniqueidentifier / binary(16))
dotnet/runtime#103658 (comment)

[edwardneal]

The real problem isn't the way that a TDS library transports GUIDs to the server, it's the way that a client generates GUIDs in the first place.

Guids are just data. SqlClient doesn't generate them at all.

I'm talking about a client-side operation, not M.D.S. Sorry for the confusion here.

We're currently in a situation where anything that is identified as a guid gets handled in a specific way and that works. What we have to add is some way to have a second handler without having a second type and that means the type information must be maintained out of band by the user.

The SqlParameter input case is mentioned is indicative of this. If you just put a guid into an object typed variable then there isn't enough information. The same problem happens in the other direction. If you have some rowdata which is typed as uniqueidentifier by sql server there is no way to know if that's old or new format. So whatever happens I don't see any other way to proceed than having special methods to both read and write the new format. We'll also need a wrapper type for object typed location like SqlParameter.

The methods you're describing make complete sense, and if that's definitely the problem to solve then I agree - it's the right way to solve it. I just don't think that SqlClient's conversion of Guids is the core problem. As I read it, the core problem seems to be that if a .NET application generates and inserts many GUIDs in parallel into a table with an index covering a uniqueidentifier column, that index will be fragmented. We'd be able to patch it within SqlClient with the method you've described, but I think the core problem is that the GUIDs being generated aren't currently "SQL Server-friendly." I've suggested implementing SqlGuid.NewId() to try to fix the problem at the point of generation.