modifying column type take too much time

Question

Hello  I have 1 table in SQL2005 with 400000 Records . I want to change column type of 1 column from char to varchar. It take too much time & not completes successfully any time . ALTER TABLE table 1 ALTER COLUMN Comments varchar(5000) null I also tried with other option like 1) Add New Column Update data in that column Remove old column Rename New Column to old column name 2) By manually changing from Select Table in object Explorer - right click - modify But can not succeed in any way. Also can not be able to use SQL job agent service due to Express version installed at client. Is there any way to reduce time in this Query in table with hundreds of record. Thanks in Advance

Suthish Nair · Answer

resolved?

Suthish Nair · Answer

Create another new table with same old table structure and with new datatype insert records then drop/rename old table. For example: From SSMS select and right click table1 --> Script Table as --> Create To --> New Query Window. Then change table1 to table2 and create new table with new datatype (run the script). Then insert into table2 select * from table1 Later rename/drop table1 --> drop table table1