Char versus Varchar (SQL Server) 1

[johnherman]

Does anyone know if there is a breakover point when deciding to use char in in lieu of varchar to save the extra overhead associated with vachar. I seem to remember a cutover at 18 for Sybase SQL Server.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[MDXer]

a good rule is that if the length of the item in the column will consistently be the length that is defined or close to it but never over the lenght.

A good example is state abbreviations set a as char(2) you know the state abbv. will always be 2 characters. Or a Phonenumber that is not defined as an integer column to allow people to use () and - you know the numbers will always be within 4 of the data column legth if you set the length to be char(14) handle max inputs like this (702)-555-1212 but even 702551212 is within reason.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[kishleo]

yeah thats the right idea, if the data in every record is of same length in that particular column you can define it as char, and if it is not same define it as varchar, because char always replaces space if there is no character in the record, if you are going to enter two characters for a field defined as char(5) it will place 3 empty spaces, so you will have to trim all the char columns before comapring, so its better to use varcahr in this case

Kishore MCDBA

 

[Garethe]

I believe the actual overhead on varchar over char is 2 bytes per row, so if your field has a range of lengths of less than 2 you're better off using char - as MDXer said abbreviations, country codes etc... are about all it's good for.

 

[johnherman]

Thanks to all for your help. Of course, the sparsity of the data will also have a great influence upon the decision to use varchar over char as well.
- John

Sometimes the grass is greener on the other side because there is more manure there - original.