Re: Can you change datatypes in columns already populated with data? View Post

[Full Discussion]
Emeka Okeke Author Jan 07, 2020
0

It depends on the datatype you are changing from, and the datatype you are changing to.

For example, if you have a column of VARCHAR(200) datatype and you try to change the datatype of that column to another column with INT datatype you will get an error.

For a better illustration, let's create a sample table below:



CREATE TABLE dbo.Tbl(ID INT,Name VARCHAR(100))
GO

and we load it with the following sample data:



INSERT dbo.Tbl(ID,Name)
VALUES
(1,'Richy'),
(2,'Don'),
(3,'Pedro'),
(4,'Mitch')
GO

Now when we try to change the datatype of column Name which is currently VARCHAR(100) to the INT datatype as follows:



ALTER TABLE dbo.Tbl ALTER COLUMN Name INT

we get the following error:

Msg 245, Level 16, State 1, Line 91 Conversion failed when converting the varchar value 'Richy' to data type int. The statement has been terminated.

As you can see, the command operation failed on the very first row with Richy name record because the Name column current takes only string values.

This means we can only change Name column's datatype to another string datatype or a datatype capable of storing string values as long as the string datatype is capable of storing the current maximum length of characters in the Name column.

For example, if you try changing the datatype again, but this time to a another string datatype that is not capable of holding the current maximum length of characters in the Name column, which is 5, as follows:



ALTER TABLE dbo.Tbl ALTER COLUMN Name CHAR(4)

--OR

ALTER TABLE dbo.Tbl ALTER COLUMN Name VARCHAR(4)

you will get the following error:

Msg 8152, Level 16, State 14, Line 91 String or binary data would be truncated. The statement has been terminated.

because you tried to change to character length of 4 when the current maximum is 5.

To see the max character length of a string column without the TEXT or NTEXT datatype, run the following query on the column:



SELECT MAX(LEN(NAME)) FROM dbo.Tbl

and for TEXT and NTEXT datatypes, use the DATALENGTH() function instead of the Len() function.

or you could just view the column's current datatype which will usually include the length argument like in the datatype VARCHAR(100). So here 100 is the maximum length of characters allowed for the Name column.

Converting Nemeric datatype to string datatype

However, the same is not through with converting numeric datatype to string datatype in T-SQL. You can actually convert a column of numeric datatype to string datatype. Let's go back to the example above. You can convert the ID column with its INT datatype to any string datatype as follows:



ALTER TABLE dbo.Tbl ALTER COLUMN ID CHAR(4)

--OR

ALTER TABLE dbo.Tbl ALTER COLUMN ID VARCHAR(4)

Nevertheless, the string datatype you are converting the column of numeric datatype to must be able to store the character length of the converted values.

However, if you try to convert a numeric column to a string datatype like VARCHAR and CHAR, with character length that is less than the maximum character length of the converted values, you won't get any errors.

Instead you will see that those records with more character length than the new string datatype's maximum length of characters will be replaced with asterix * value.

For example if we insert additional record into the dbo.Tbl table as follows:



INSERT dbo.Tbl(ID,Name)
VALUES
(2000423,'Jon')

and then execute the following code:



ALTER TABLE dbo.Tbl ALTER COLUMN ID VARCHAR(4)

we will see the following entry for name Jon in the ID column:

ID	Name
*	Jon

So be careful when converting columns of numeric datatypes like INT and DECIMAL(10,5) to string datatypes. In summary, it all boils down to the datatype you are converting from, the datatpe you are converting to, and if the new datatype have enough space to store the old datatype's values.