Low level semi academic datatype question: Char vs Varchar?

[jsteph]

Hi all,
I'm wondering, on a somewhat academic level, if there is a noticeable and/or useful performance gain in database speeed between char and varachar of the same size dimension.

For example, if I have an indexed field that I know the be from 1 to 6 characters, if at the application level I know all the data in that char(6) field will be left-zero padded, would it be more efficient than if that field was varchar(6). The data in the varchar field would be, for the number 1 "1", but in char it would be "000001", and so on.

I'm specifically looking at when that field is joined to a foriegn key field of the same type. That is, if the database knows that the fields being joined are always going to be uniform 6 characters, will there be some peformance improvement knowing that fact--as opposed to the db engine having to compare one table's field that may be 1 character and the other tables joined field might be 2, 3, 4, 5 or 6 characters, and vice versa and so on.

I know it sounds like a silly question and any difference may be insignificant, but I wonder if this topic has come up and if there's any thoughts either way.
--Jim

 

[foxdev]

Generally, use VarChar if there is significant variance in the actual stored data widths; using VarChar in these circumstances can lead to more efficient storage and fewer reads. Use Char if the data values don't differ much in length, as SQL Server can process fixed-width columns a bit faster.

Again, these are generalizations; YMMV.

Also consider using integers as your keys so that you join on those rather than string values.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[jsteph]

foxdev,
That sounds good to me, thanks,
--Jim