Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

can you point me in the direction of where that myth was debunked? (I agree that the advice doesn't apply to SQL Server)
–
Andrew BickertonApr 1 '11 at 9:59

I'm also referring to one of the original versions of DB2 (probably hasn't been this way in the last 10yrs), similar to the advice with SQL 2000 that PKs should be non-clustered because of performance issues still being around - though no longer being true
–
Andrew BickertonApr 1 '11 at 10:11

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.