I've recently inherited a SQL Server database that uses
BINARY(16) instead of
UNIQUEIDENTIFIER to store Guids. It does this for everything including primary keys.
Should I be concerned?
Well, there are a couple of things here that are a little concerning.
First: while it is true that a
The three behavioral differences that I can find are:
Second: based on the following statement
I would be concerned in general for system performance by using GUIDs as PKs instead of as Alternate Keys along with using an
The following comment, made by the O.P. on @Rob's answer, brings up an additional concern:
GUIDs can be stored in 2 different binary formats. So, there could be cause for concern depending on:
The issue with where the binary representation was generated has to do with the byte ordering of the first 3 out of the 4 "fields". If you follow the link above to the Wikipedia article, you will that RFC 4122 specifies to use "Big Endian" encoding for all 4 fields, yet Microsoft GUIDs specify using "Native" Endianness. Well, Intel architecture is Little Endian, hence the byte order for the first 3 fields is reversed from systems following the RFC. The first field, "Data 1", is 4 bytes. In one Endianness it would be represented as (hypothetically)
The concern with ordering is simply that they won't be in the same order after converting to
The concern with the string values being used outside of the database is more serious, again, if the binary representation was generated outside of Windows / SQL Server. Since the byte ordering is potentially different, then the same GUID in string form would result in 2 different binary representations, depending on where that conversion took place. If app code or customers were given a GUID in string form as
So, if the GUIDs never left the database then there isn't much to be concerned about outside of ordering. Or, if the import from MySQL was done by converting the string form (i.e.
In all likelihood there won't be an issue, but I am mentioning this because under the right conditions there could be an issue.
And how do new GUIDs get inserted anyway? Generated in the app code?
If the previous explanation of the potential issue related to importing binary representations of GUID generated on another system was a little (or a lot) confusing, hopefully the following will be a little clearer:
In the output shown above, the "String" and "Binary" values are from the same GUID. The value beneath the "Binary" line is the same value as the "Binary" line, but formatted in the same style as the "String" line (i.e. removed "0x" and added the four dashes). Comparing the first and third values, they aren't exactly the same, but they are very close: the right-most two sections are identical, but the left-most three sections are not. But if you look closely, you can see that it is the same bytes in each of the three sections, just in a different order. It might be easier to see if I show only those first three sections, and number the bytes so it is easier to see how their order differs between the two representations:
String = 15F2ED3234BE – 5E562C – 7408EE
So within each grouping, the ordering of the bytes is reversed, but only within Windows and also SQL Server. However, on a system that adheres to the RFC, the binary representation would mirror the sting representation because there would not be any reversal of the byte order.
How was the data brought into SQL Server from MySQL? Here are a few choices:
Assuming it was straight binary-to-binary (i.e. Convert #2 above), then the resulting GUID, if converted to an actual
Which is wrong. And that leaves us with three questions:
You can always be concerned. ;)
The system may have been migrated from some other system that doesn't support uniqueidentifier. Are there other compromises you don't know about?
The designer may not have known about the uniqueidentifier type. What other things didn't they know about?
Technically though - it shouldn't be a major concern.