Take the 2-minute tour ×
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

share|improve this question
2  
Possible duplicate of question How do too long fields (varchar/nvarchar) impact performance and disk usage? (MSSQL Server 2008). If you think it's not a duplicate, then definitely you'll find some answers there too. –  Marian Apr 1 '11 at 7:09

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
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. –  Andrew Bickerton Apr 1 '11 at 8:54
    
@Andrew Bickerton: yet another myth... –  gbn Apr 1 '11 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) –  Andrew Bickerton Apr 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 Bickerton Apr 1 '11 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 '11 at 8:41

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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