Using Unicode double-byte datatypes such as NCHAR, NVARCHAR and NTEXT doubles disk space, so avoid them unless you really need them. Use VARCHAR instead of CHAR, unless your data is almost always of a fixed length, or is very short.
SQL Server fits a whole row on a single page, and will never try to save space by splitting a row across two pages.
Running DBCC SHOWCONTIG against tables shows that a table with fixed length columns takes up less pages of storage space to store rows of data.
General rule is that the shorter the row length, the more rows you will fit on a page, and the smaller a table will be.
It allows you to save disk space and it means that any retrieval operation such as SELECT COUNT(*) FROM, runs much quicker against the smaller table.
We have a product called SQL Auditor that can produce a report indicating unicode datatype columns in your database. It also can generate
It also can generate sql script to change datatype of those columns from nchar to char and from nvarchar to varchar. To change
To change datatype of ntext columns we recommend to use Enterprise Manager.
Figure: Use Enterprise Manager to change datatype of ntext columns.