Abhishek
Abhishek

Reputation: 291

How to change column type in sql server 2008

How to change data type of column in sql server if the column contain millions of rows?

I try this it should work.

 alter table employee
 alter column dob datetime

but I get an error.

That is - table contain huge amount of data or it is full you are not able to change data type.

Upvotes: 1

Views: 501

Answers (1)

cyberhubert
cyberhubert

Reputation: 203

Please take following steps:

  1. create new column with desired datatype and new name
  2. update values of that column with converted values from the old one
  3. remove the old column
  4. rename the new one to the old name

If you go this way, you can see, where the problem is:

  • step 1 - you have no rights?
  • step 2 - there is some problem with conversion
  • step 3 - I guess there is some foreign key or index which holds this column in place

Upvotes: 1

Related Questions