Alter Colum Datatype from Int to Ntext and vice-versa

Question

Hi Experts,

I have a Sql table

CREATE TABLE [UserTable]
(
    [ID] [int] NULL,
    [Name] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL
) ON [PRIMARY]

In this table column ID have the Datatype Int, I want to alter the data type of the ID column to Ntext, For that I am using the following Sql Query:

ALTER TABLE UserTable
ALTER COLUMN ID NTEXT

This query gives the following error message:

Msg 206, Level 16, State 2, Line 1 Operand type clash: int is incompatible with ntext

While when I alter ID column Datatype from Int to navarchar it works fine.

After that I am trying to Alter ID(now the data type of the Id column is Nvarchar) Column data type to Ntext by Alter Query it alter's ID column data type successfully.

Why we can not alter column name data type Direct Int to Ntext, While we can do this via INT to Nvarchar after that Nvarchar to Ntext.

Any suggestion will help me a lot.

Thanks Vijendra Singh

Answer 1

You're trying to do an implicit conversion which is disallowed from int to ntext. Why, I don't know, but I guess because it's 2 steps (int->string, string->LOB).

Also, don't use ntext. It's deprecated. Use nvarchar(max) which should allow implicit conversion.

Finally, if ID is the primary key then why do want a LOB type? You do have a primary key...?