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.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-15 : 05:45:12
|
| Hi,There is a table with 50 Million records.One of the fields has a datatype of varchar(50) which needs to be changed to char(4).I have the script which takes care of this change...i.e. alter table xxxQuestion:How can I be sure that because there are alot of records, then the system will not fails half way through running the script and give an error message i.e. there is not enough space (I think it might mention something about tempDB), etc ?So, I want to be sure that I will not get any issues on space. I think this could be to do with log files?Any thoughts please?At present the .ldf file has initial size of 78 GB and set to autoincrease by 10%Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 05:48:30
|
| This is quite a major change - variable length to fixed length.I would consider not doing it.If it had to be done I would probably create a new table and copy the data into it in batches or bcp out and in - then update for any changes that had been made in the meantime if the system couldn't be stopped.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-15 : 06:14:25
|
quote:
Originally posted by nigelrivett
This is quite a major change - variable length to fixed length.I would consider not doing it.
Hi, 1- Can you explain why not to do it?2- will doing it, gain in performance or space used?Thanks |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-15 : 06:25:23
|
| Cause are you sure that in your existing column the datalength is equal to or less than 4 char ?PBUH |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 06:44:36
|
| Should give a small gain is space used if the data is exactly 4 characters. If it is not then you may have functional issues that the column will now be space padded and also may take up more space.May give a gain in performance if it is joined to a char column on another table - but you might want to change to an integer on both tables in that case.I wouldn't do it because of the effect it will have on the system for probably not much benefit.Also have a lok at thishttp://www.nigelrivett.net/SQLAdmin/AlterTableProblems.htmlThat was from an old version of sql server where alter tables to alter columns was a very bad idea. It's worth checking if this is still the case and how it applies to you.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
change datatype of a field