mercredi 28 janvier 2015

Impact of changing char(10) to varchar(12) on massive table


I have a table in a database in Microsoft SQL Server 2005 server that is 16gb in size and has 58 million rows.


It has a column called 'balance_forward' that is char(10) (Unusual choice for a numerical column but I didn't design the table) I need to increase its size to accommodate balances that are more than ten characters in size (xxxxxxxxx.xx)


I tried changing it to char(12) (risky, I make no excuses) but I think this caused the database's log file to grow many gigabytes larger (and fill up the log drive) and the operation failed anyway (data type is still char(10))


I later realized that it would make more sense to change to at least varchar(12), that way the column won't forcibly take up more space, but it will have more room to fit larger data.


My question is - will this also cause the log file to grow again (I managed to free up some space by moving other files off the log drive)


And am I correct to assume that using varchar instead of char will prevent the existing data from taking up more space?


(Ideally the data type should be changed to the most appropriate type for financial balances, but I figure that will be a more drastic change on 58 million rows)





Aucun commentaire:

Enregistrer un commentaire