March 10, 2006 at 9:02 am
Hi:
We have a table that has one of the fields defined as:
[field1] [char] (20) NOT NULL
Currently, this table has > 26 million records. Most of the records have just got a 3-character value for this field, like 'ABC'.
If we were to run this query:
ALTER TABLE mytable ALTER COLUMN field1 varchar (20) NOT NULL
Will it result in reducing the data file (MyDB_Data.MDF) size?
Thanks,
AJ.
March 10, 2006 at 9:21 am
Not until you go through and remove all the trailing spaces from all the records that are < 20 chars long.
That could take some time. But new records will not be taking up the additional space.
March 10, 2006 at 9:25 am
Changing the datatype from char to varchar will reduce the amount of space each row of data in that table will require for storage. Datatype Char reserves a set amount of space each time a row is created. Datatype Varchar will only reserve enough space to hold the data that it needs to hold. With this said, changing this alone and removing the trailing spaces will not reduce the physical size of your MDF file unless you shrink the file (or if autoshrink is enabled which is not recommended). Unless you have the need to shrink your data file, I would recommend that you leave it alone. Changing the datatype will re-claim a good amount of space, but your MDF file should remain the same size.
March 10, 2006 at 9:34 am
well - a quick update with rtrim should take care of that...
**ASCII stupid question, get a stupid ANSI !!!**
March 10, 2006 at 9:56 am
OK, so if I was to run these queries in sequence:
1. ALTER TABLE mytable ALTER COLUMN field1 varchar (20) NOT NULL
2. UPDATE mytable set field1=rtrim(field1)
and then do a shrink database operation, should I expect that the MDF file to drop in size? Right now it is > 16GB and we really need to bring the size down. So we are going through every table to determine which char fields can be set to varchar.
Thanks,
AJ.
March 10, 2006 at 12:33 pm
Yes, that will have the desired result. However, your log file is going to blow up when you do it, so make sure you have plenty of space for the log available.
Also, read BOL for a list of restrictions on the use of ALTER COLUMN. It will not work if, for example, you have an index or a constraint on the column.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply