I am trying to change a column's datatype. The column of type VARCHAR has thousands of GUID values like look those shown below:

b1f4ff32-48d4-494e-a32c-044014cea9
bc5a1158-b310-49ff-a1f3-09d4f8707f69
4b7ebc9d-9fa1-42d9-811e-0b7b4b7297a
fc7ba848-98ea-4bc6-add7-11f0ee9c6917a21
485741ff-2ab2-4705-91b3-136389948b7c

I need to convert the column type to unqiqueidentifier using the script below. Can I do that safely without corrupting the column data?

alter table MyTable
alter column guidColumn uniqueidentifier not null

CodePudding user response：

If you change the data type SQL Server will first check if all the values in the columns can be implicitly converted to the new data type; if they cannot then the ALTER will fail. If they can, then they will be implicitly converted and the ALTER will be successful (assuming no dependencies of course).

For a uniqueidentifier then either it's a valid value or it's not, so either the data will all convert or the ALTER won't take place. For something like a date and time data type, however, you could very easily end up with incorrect data if the data is stored in an ambiguous format like dd/MM/yyyy. This could mean a value like '12/05/2022' ends up being stored as the date value 2022-12-05 rather than 2022-05-12. For such scenarios you would therefore want to UPDATE the data to an unambiguous format first, and then ALTER the data type of the column.

CodePudding user response：

The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type.

Also there are limitations, uniqueidentifier type is limited to 36 char

So if you decide to truncate the table like in this example:

DECLARE @ID NVARCHAR(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';  

SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;

This will be the result:

So, if your string is more or less than 36 it will not truncate correctly.

For more information check Microsoft documentation: https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-ver15