Searching CHAR or VARCHAR datatype

Question

Searching CHAR or VARCHAR datatype

FreeHansje

SSChampion

Points: 11751
More actions
July 25, 2005 at 3:35 am

#89210

I usually use VRCHAR when storage of 3+ characters is needed. I got into an argument with a collega about another consideration besides storage: speed of searching thru such a column. The fixed length of a CHAR datatype speeds searches on this column, because of its fixed length. Similar, searching on a VARCHAR column would be a bit slower, because of its non-fixed length.
I have Googled the internet, found a reference(only 1!) to this, but I am curious on how much difference this really would make. In what circumstances would you choose a longer CHAR column in favour of a VARCHAR datatype? Is there some formula that gives me an indication to judge by?
Greetz,
Hans Brouwer

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

I would always tend towards the varchar unless I was 100% certain that a field would always be an exact length no-matter what, at which time I would consider changing to a char instead (but still only consider because developers tend to change their minds at the drop of a hat).

FreeHansje SSChampion Points: 11751 More actions · Answer 2

I agree, it still leaves open the question about search speed. Anyone has experienced problems related to VARCHAR vs CHAR datatypes?

Greetz,
Hans Brouwer

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

I would be very interested in seeing that reference. In any case I do not think any difference in search performance would be noticable in almost any scenario you could set up.

Another thing to notice is that a varchar column (any varying size column actually) will use an extra 2 bytes storage (4 if it is the only varying size column). That I think would be more possible to notice than a search performance difference.

Anyway, in my opinion you choose the datatype that is best suited for the data you will be storing. If you have a fixed-length string you use char, otherwise varchar.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

noeld SSC Guru Points: 96590 More actions · Answer 4

I have tested his many many times for skeptic developers that needed proof all the time and I can assure you that there are no performance benefits whatsoever in searching!! I/O will matter the most for timing purposes and the possible extra CPU cicles consumed are dwarfed by the I/O times. If your data is of varying nature you have to go with varchar if not and is very small char may make sense!

Here are some pointers form Kalen Delaney's Inside SQL Server 7 which still apply for 2000:

Variable-Length vs. Fixed-Length Datatypes

* Noel

FreeHansje SSChampion Points: 11751 More actions · Answer 5

Tnx for the responses all. I saw a discussion on faster searchtimes in a MySQL forum, could not find it anymore.

Greetz,
Hans Brouwer