I just discovered a problem in the database design for an application that was developed years ago. There is an audit table with OldValue and NewValue columns defined as nvarchar(255). However, in the mean time, new tables have been defined with columns longer than nvarchar(255).
I want to propose that the audit table use a much larger value.
However, nvarchar takes up twice as much space as varchar, AND not too long ago, there was a forum post about trailing blanks in nvarchar, and how that can be a problem in comparisons, etc. unless you use RTRIM().
So I was wondering what the general consensus is on using varchar versus nvarchar, especially for longer strings. Is it considered best practice to always use nvarchar, or are there some other considerations?