- #Migrating mysql uuid to mssql uniqueidentifier update
- #Migrating mysql uuid to mssql uniqueidentifier plus
Regarding the update to the question in which it was clarified that the intended datatype to change to is really UNIQUEIDENTIFIER and not NVARCHAR(36): the overall advice does not change from "do not do it". And, if it has to be stored as a string, then use VARCHAR given the only characters are A, B, C, D, E, F, and - ( NVARCHAR is a completely unnecessary waste of space) and be sure to specify a binary Collation (one ending in _BIN2).
If you need to have a GUID to have a value that is know to external systems, just add a UNIQUEIDENTIFIER column and index it you can then look it up to get the INT value to use for all other JOINs, etc. This need competes with other queries, plan cache, etc.
That means more time to read them from disk into memory, and obviously the more memory they will require. Larger rows and/or lower FILLFACTOR means more data pages are required to hold those rows. the Buffer Pool) before their row(s) can be read and used. Is the FK column indexed? If yes, then that is another 72 bytes instead of 4.ĭata pages are loaded into memory (i.e. On the other hand the current INT would only be 4 bytes * 2 = 8 bytes. If this PK is used in 2 other tables as a Foreign Key, then that is 72 bytes * 2 = 144 extra bytes. PKs are often used by FKs, which is the PK copied into one or more other tables.
#Migrating mysql uuid to mssql uniqueidentifier plus
On the other hand, the current INT would only be 4 bytes * 3 = 12 bytes plus the original 4 = 16 bytes total. Three non-clustered indexes on this table is 72 bytes * 3 = 216 bytes plus the original 72 of the Clustered index = 288 bytes total. Assuming, again, that this PK is Clustered, then each non-clustered index on this table will have that 72 byte value copied into it. Please keep in mind the down-stream negative effects of this change due to:Ĭlustered index keys are copied into non-clustered indexes. And if you decrease FILLFACTOR to reduce the number of page splits, you are also reducing the performance of the index as it is spread out over a larger amount of pages. Highly Fragmenting: new values are all over the place, leading to greatly increased page splits.Using a case-insensitive Collation, or even a case-sensitive one, is definitely slower as it will apply locale-based linguistic rules. Latin1_General_100_BIN2) to at least make the comparisons byte-by-byte since linguistic rules aren't necessary. Finally, most people storing GUIDs as strings forget to use a binary Collation (e.g. Storing this as a string is now a 36 character comparison (36 bytes in VARCHAR and 72 bytes in NVARCHAR) which is slower than the 16 byte UNIQUEIDENTIFIER. Inefficient: If using UNIQUEIDENTIFIER it is not so bad as that is still a binary comparisons, just like with INT, but is 16 bytes unlike INT which is 4.Wide: 72 bytes per row (GUID is 16 bytes, which is 32 characters in string form for the hex values plus 4 dashes for 36 characters total, which is 72 bytes in UTF-16 / NVARCHAR).You are wanting to change this to a Clustered PK that is: Minimally Fragmenting: new values are sequential and added to the end of the table.Efficient: comparisons are simple 4-byte-value-to-4-byte-value (i.e.You currently have a PK (Clustered, I assume) that is: You do not want to make that change as no good can come of it. Every tables of my database has id primary key column and I want to change them to nvarchar(36) with default newID()
0 kommentar(er)
