Changing nvarchar to varchar makes database increase in size?

[btaber]

I using the ms access upsizing wizard, it made all of the text fields nvarchar. Now if I understand, nvarchar allows for storage of unicode in the fields, so each character takes up 2 bytes. So I altered the tables to just varchar, and the database got larger. Am I missing something?

Brian

 

[rac2]

That is what Microsoft Wizards do, they make things bigger.

OK, not very funny. Are you judging by the size of the mdb file? My hunch is that the size of that file was increased in order to copy the data from the old tables to the new tables. The old tables were deleted but the disk space was not released. This leads me to think there may be an Access utility function to recover empty space. It would be something equivalent to unloading and reloading the database. You know, I am not even 100% confident that your version of Access uses the mdb file extension. That may be old hat. You might get a better answer by posting in one of the Access fora.

 

[nigelrivett]

See

http://www.nigelrivett.net/AlterTableProblems.html

You probably end up with the old data still in the table just not referenced in syscolumns and it will have been copied to the end of the row.

I would advise against alter table alter column.

If you did it via enterprise manager then try it on a test table and check the script. It will probably have created a new table, copied the data into it and then deleted the old and renamed the new. The database would have expanded to hold both copies of the table and may now need to be defragged and shrunk.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[btaber]

thank you nigelrivett. Sounds like Microsoft's way of doing things. to what rac2 said, I was looking at the properties of the SQL Server database. There is only one column I have to change so I will leave the other ones alone. Thanks.