Alter column datatype.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alter column datatype..

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2009-01-07 : 10:53:14

Hi,

I need to, for some reason, change the data type of a column from varchar(120) to varchar(20).
Now this table has around 40 million rows in it.
I used the "alter table alter column" command for the same, but this is taking forever to happen!
Is there any way I can make this happen faster?

Thanks
Sam

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-07 : 10:57:50

Not that I know of. 40M records is quite a bit to process, so it's all up to your hardware at this point.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 10:58:58

one way is which u are trying with query

ALTER TABLE xxx ALTER Column YYY VARCHAR(20)

Another way is

Right click on the table and go to design mode ---> there change the datatype of required column

xpandre
Posting Yak Master

212 Posts

Posted - 2009-01-07 : 11:07:24

Thanks Skorch. I was wondering if anything could be done at the code side :=)

Raky - I would need a script for the same, so am not doing the same in design mode. Moreover, I think both would take the same amount of time for execution, isn't it?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 11:18:12

quote:
Originally posted by xpandre

Thanks Skorch. I was wondering if anything could be done at the code side :=)

Raky - I would need a script for the same, so am not doing the same in design mode. Moreover, I think both would take the same amount of time for execution, isn't it?

I didn't tested because i don't have a table with such large no.of records. I Guess it may take less time in Design mode. Why don't u try this possibility from ur side?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 12:00:52

quote:
Originally posted by raky

quote:
Originally posted by xpandre

Thanks Skorch. I was wondering if anything could be done at the code side :=)

Raky - I would need a script for the same, so am not doing the same in design mode. Moreover, I think both would take the same amount of time for execution, isn't it?

I didn't tested because i don't have a table with such large no.of records. I Guess it may take less time in Design mode. Why don't u try this possibility from ur side?

i dont think that will make a difference. Internally processing is same even if you do via query or via designer window.

xpandre
Posting Yak Master

212 Posts

Posted - 2009-01-08 : 02:08:40

Hi Guys,
In continuation to this, I would even need to truncate the data in the column to 20 chars before I go ahead with the "alter table" clause.
I was thinking of using SUBSTRING for that. But I think this would further hamper the performance.
Is there any better way to truncate the data to 20 chars?

Thanks
Sam

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-08 : 02:15:12

try first creating a new column, update the column with properdata and drop old column and rename the column
some thinglike

ALTER TABLE	<tblName> ADD <NewColumn> VARCHAR(20)
GO

UPDATE <tblName>
SET <NewColumn> = SUBSTRING(<OldColumn>, 1, 20)
GO

ALTER TABLE <tblName> DROP COLUMN <OldColumn>
GO

EXEC sp_rename '<tblName>.<NewColumn>', '<OldColumn>', 'COLUMN';
GO

Before doing this set the Recovery mode to Simple

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources