9

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

1

3 Answers 3

8

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.

5
  • under DB2 and some other DB engines char and varchar were stored differently and some of the advice for DB2 transferred across to SQL Server. Apr 1, 2011 at 8:54
  • @Andrew Bickerton: yet another myth...
    – gbn
    Apr 1, 2011 at 9:11
  • can you point me in the direction of where that myth was debunked? (I agree that the advice doesn't apply to SQL Server) Apr 1, 2011 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 Apr 1, 2011 at 10:11
  • @Andrew Bickerton: simply look at on disk structures of SQL Server (several SO answers from me) stackoverflow.com/search?q=user%3A27535+anatomy. Sorry for late reply
    – gbn
    May 29, 2011 at 8:41
3

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.