'Does altering column type corrupt the column's existing data?

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
sqlsql-servertsql


Solution 1:[1]

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.

Solution 2:[2]

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:

String Truncated Value
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong 0E984725-C51C-4BF4-9960-E1C80E27ABA0

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Larnu
Solution 2

Related Questions