Friday, February 10, 2006

Overlooked Database Space Optimization

While looking for places to cut down the size of this huge database that I'm working with, I realized that all strings were being stored in nvarchar fields, which uses Unicode, or 2-bytes per character. This is likely because the database was originally prototyped in MS-Access, and when they imported all of the Access data into SQL Server 2000 using DTS, it transformed the Text fields into nvarchar fields.

Well, it turns out that all of the string data is ASCII anyways, so I was able to nearly cut in half the storage requirements of the database by changing the nvarchar fields into regular varchar fields. This also had a positive affect on the runtimes of my stored procedures that moves data around within the database.