Just to weigh in a little late on this one.. 

Developmental speed can often outweight a fully-optimized data source.  I.e. if all dates are "datetime" and all strings are "varchar" then it can be faster and simpler to design the apps that sit on top of them.  I'll admit it's not as optimized as it could be, but there's a huge cost savings in knocking a month out of a development cycle that could easily buy a whole rack of SCSI drives.

I would personally only use this type of optimization on something where bandwidth was absolutely critical such as a wireless app with very limited bandwidth.

JT Lovell

You bring the most important point. We all do the same to increase the speed of development because most of the project has a deadline of yesterday. This is the reason after development and use of the application for several month we can run this type of routine to fine tune the database and it will not take a month and we will save the whole rack of SCSI drives and do not forgot the cost of backup also.

Thanks
Amit

Thank you Amit.

I use S-S 2000.

I ran your entire set of code, but nothing was written to the ouput table. Is your step for 2005 or 2000?

But on the subject of data types, my biggest pet peeve is the over-use of the nvarchar type here at my office.

Oh my, it is so wasteful. For some reason, when developers bring over a table from Access, the SQL table will always have several nvarchar columns which I then have to "clean-up" by changing each to varchar.

A 10-character value stored in an nvarchar column actually uses/occupies 20 bytes of disk space and memory/RAM.

I have recovered several Gigabytes of disk space by simply changing data types in several tables.

**If anyone knows how to change the DEFAULT data type for Access' Text columns when importing them into a SQL table, I would sure like to know that.** 

I know there are times when nvarchar is required but in my environment - almost never.

Thanks. John