In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any memory/performance gain from changing 1-3 varchar(5) columns to char(5) columns on a table with
a) say 2.000.000 rows
b) if any of the varchar columns are part of indexes / the primary keys
You are confusing text/varchar(max) (LOB types) with varchar(n)/char(n)
Otherwise, if you use char(1000) not varchar(1000) and the average data length is, say, 42 this is bad design and a lot of wasted space.
It is hard to tell you about specific performance gains or hits without knowing more about your schema, but in general you want to define the dataype to match your data. If you have the need for a fixed width (e.g., you know you will always have five characters) then you should use char(5). If you have a variable amount of data then you should use varchar(5).
For SQL, the more data you can fit on a page, the better your performance.
As far as I know SQL Server has never stored varchar or nvarchar data types out of row. Text and NText are different. There is an extra few bits which are stored for a variable width column but that's about it.
