January 16, 2008 at 12:15 pm
Hello all!
We have a medium sized db with about 100 tables most are small and max out around 500k records in the biggest table. We converted from Access a few years back and just accepted the datatypes assigned by Microsoft during the conversion.
Recently looking over what we did we decided we needed to get away from the nchar/nvarchar datatype usage in favor of char and varchar. In reading different forums it has been stated that the n (Unicode) datatypes use 2x the space of non-Unicode datatypes. All fine and good that info pretty much convinced us we needed to make a change.
So we picked a table with 1k records and changed two fields from nchar to char and nvarchar to varchar and let sql save and convert the field contents.
Having checked the table size and index size before the change we were surprised to see that the table size actually increased as did the index size after the change was made! The size increase was not enormous but seemed to be around 15 to 20% bigger.
Can anyone shed some light on why the details would indicate an increase in sizes when, IMHO the sizes should have been smaller?!?!?!
Thanks very much for any info!
Rich
January 16, 2008 at 12:57 pm
Have you tried rebuilding the clustered index on that table? Just curious.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 1:10 pm
Thanks for your interest. I hadn't rebuilt the clustered index so I went and rebuilt all indices and there was no change to the sizes for either the index or datafile
Rich
January 16, 2008 at 1:35 pm
you should save 50% space making this change unless somehow the columns have padded with white space. How are you measuring the size of the table?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 16, 2008 at 2:17 pm
To find the details I right click the table then Properties / General / Storage and look at 'Data Space' and 'Index Space'.
Logic says that indeed the physical space should be smaller but it isn't. I'm going to do another table and post the results.
Thanks for the reply,
Rich
January 16, 2008 at 2:28 pm
Richard - try the Disk usage by table report (right-click on the DB, standard reports). You may find that while the data space went up, the "available space" also went up.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 24, 2008 at 3:39 pm
Thank you for the reply. I went to see what I could find on the reports and realized that we haven't yet installed SP2 (the reports weren't where I'd read they should be). I've put things on hold till I can get and install SP2 over some weekend.
Thanks for your help and if SP2 doesn't get things back on track, I'll probably repost here.
Thanks everyone!
Rich
January 25, 2008 at 2:23 pm
Actually in the converting of the columns, the SSMS (and most other tools) will duplicate the table, copy the data into it, and then drop the old table.
This process with grow the data files to almost 2x 😀
This is usually discouraged, but in this case it is probably appropriate. Shrink your db's through the interface.
After doing so you should probably go back and rebuild your indexes too (It will cause some growth again). This fixes most of what the shrink screws up.
June 11, 2008 at 12:49 am
Is there any Performance issue while converting Char/varchar to nchar/nvarchar?? Can any body suggest it??
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply