Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Joozh
Posting Yak Master
145 Posts |
 Posted - 2006-04-18 : 04:56:58
|
| Hi all,Suppose I know that the maximum length for my customerID field will not be more than 10 characters (95% of times this will be numeric digits like 1000127, while 5% of the times this may be alphanumeric like 1001MT56).Given this scenario should I use char(10) as the datatype for my customerID field or should I use varchar(10). Can you kindly share your expert advise from a performance aspect. BOL has a lot of information but I could not the exact answer to this "kiddy" questions and thus ended up posting this question here.Thanks & Regards.-J |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-04-18 : 05:41:28
|
| If it has a max of 10 chars, but will most likely be smaller than that, then use varchar, if it will definitely always be 10 chars, then use char.. |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-18 : 16:07:57
|
| The one thing to be careful of when using CHAR fields is how the field is filled in by the server. A CHAR (12) field that gets the value ' abcd' input to it, has to be checked for equality as ' abcd', whereas if you made the field a VARCHAR (12) you'd just input 'abcd' and could always query against it as 'abcd' without having to type the leading spaces. Another issue that is very rare regarding things like this is say you have a page of data that has 10 rows in it. If you update a VARCHAR field it could push the storage passed the max for the page, and thus a record then has to be shifted to a new page, and indexes have to be updated. With a field that is likely WRITE ONCE/READ MANY that won't be an issue. But if the field is going to be constantly be updated like COMMENTS or something, then you want to consider the ongoing page/indexing issues associated with the field bopping around. No such thing as a "kiddy" question in the real world, so ask away.Dalton |
|
|
|
a_r_satish
Yak Posting Veteran
84 Posts |
Posted - 2006-04-19 : 04:58:59
|
| for constant sized variables use char datatype and for others use varchar..To be more app, when u know the columns to have value with same size then char, else varchar..satish.r"Way to success is always under Construction" |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2006-04-19 : 05:45:04
|
| Thanks everyone for your replies :)Thanks & Regards.-J |
|
|
|
|
|
|
|
|
Kinder garden question about datatype