Best way of changing data type 2

[micang]

SQL 2000

Hi All,

If I have one column that I would like to change from varchar to numeric (and it only does hold numeric values), what is the most efficient way of doing this?

The way I have been doing it is by chaging the data type in EM. Is it better to do this using T-SQL? Could someone guide me on the syntax if this is indeed the better way of doing it?

Many thanks.

Michael

 

[Crowley16]

if you're happy doing it in em then stick to it, however if you want to script it out, then you'll need to read up on the ALTER TABLE statement in bol...

--------------------
Procrastinate Now!

 

[micang]

Thanks Crowley16.

Reason I ask is because the field has 50 million rows, so not sure if EM is the ideal way of doing this.

Thanks for the lead.

Michael

 

[Eyespi20]

Either way will take the same amount of processing time and i/o, so whichever way is easiest for you do do. You're talking about a once and done thing here.

Just be sure to backup before you do it just in case.

Margaret

 

[micang]

Thanks for the input Margaret, much appreciated.

Michael

 

[gmmastros]

Let's be clear about something.

When you talk about numerics, do you really mean integer? Do any of your numbers have a fractional component to them?

If your values are whole numbers, then I would suggest you convert this column to integer. Integers have a slight advantage over numerics because it usually takes less bytes to store the data. If you're lucky, you may be able to store more records per data page, which would reduce your file I/O and improve performance.

You should check to see if all the values are valid numbers before chaning the data type.

[COLOR=blue]Select[/color] ColumnName
[COLOR=blue]From[/color]   [COLOR=blue]Table[/color]
[COLOR=blue]Where[/color]  [COLOR=#FF00FF]IsNumeric[/color](ColumnName + [COLOR=red]'.0e0'[/color]) = 0

The query above will identify those records that cannot be converted to integer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Michael,
IMHO
It is usually better work with TSQL instead of making changes through the GUI. Do you know if you ALTER a table through EM it actually creates a new table, inserts the data, renames the new.

you want to use
alter table <table_name>
alter column <colum_name>

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi George and Paul,

When you talk about numerics, do you really mean integer? Do any of your numbers have a fractional component to them?

The data is engine cubic capacity eg 01988. Therefore I think as you suggest integer makes more sense? All the numbers will be whole. I will perform the check as you suggested, i was not aware of how to do this.

Paul, I thought that was the case, that T-SQL is better. I looked at the script from EM, and as you say it creates a completely new table (which I didn't know)! I have a table with 35 columns and 50 million rows, it will take quite some time doing this!

Therefore using the alter table/column makes complete sense, as it will only deal with the column in question.

A star for both of you as you have taught me alot in one post.

Michael