Hi,
This question may sound silly,but please comment.
Please tell me a situation where char should be used and not varchar.
Let us assume that we are dealing with non unicode characters.
Well, I find varchar is always smarter than char, so why char?
Thanks!!
Rudrafixed length identifier fields? even though smart numbers are stupid.|||use CHAR(n) instead of VARCHAR(n) when n<4
consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
what did you mean by "smarter" anyway?|||fixed length identifier fields? even though smart numbers are stupid.
Yea, I have seen those in fixed length identifier fields,any more use of char?
Well, in database designing which attributes are assigned as char?
If we don't use all the characters tehn its a mess...what are the best situation to use char? Plz comment...
It may seem a silly one but I think it has an important significance in database designing...:)
Thanks!!
Joydeep|||use CHAR(n) instead of VARCHAR(n) when n<4
Yea thats a very good point...:rolleyes:
and
consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
that should be applied when and only when n<4.Isn't it?
ok,thank you for those info.
what did you mean by "smarter" anyway
by smarter I mean to say varchar though variable length does provide more efficient storage than char and also doesn't need any trim functions to compare...and many more are there...:)
Thanks!!
Joydeep|||yes, those are advantages for VARCHAR, good points
there really isn't any reason to have CHAR, when you think about it
i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs|||i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs
LOL,:D
rudy.ca... its cool and your pic too :)
Thanks again r937|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.
In general, I prefer to use VARCHAR, but there are times and reasons to use CHAR, and I wouldn't want to be without it as a choice.
-PatP|||Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.
-PatP
And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:
Thanks Pat
Joydeep|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.:rolleyes:
Thanks Pat
Joydeep
I would say using CHAR in the zipcode is a good idea. I'm in Canada and we have postal codes that contain letters and numbers. Not only that, they are 6 characters long! I've seen some pretty bad e-commerce sites that wouldn't let me put in my address because their "zip code" field wouldn't let me enter the last character of my postal code.|||And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fieldsWell, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters. Any time you have a fixed-length string (such as a five digit zip code or a nine digit social security number) CHAR is more appropriate and more efficient than VARCHAR.|||Well, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters.i do believe i said that quite early in the thread :)
Any time you have a fixed-length string (such as a five digit zip code ...in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your app
whereas with CHAR(5) for the zip code, you're screwed
another fine example of the one of the many benefits of VARCHAR
;)|||i do believe i said that quite early in the threadGreat advice is worth saying twice, eh?
in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your appI'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...|||I'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...oh you silly man
okay, either you are consistent and silly, or else you are inconsistent and pragmatic, but please don't use "normalization" as an excuse for rationalize it either way
do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'
do you put apartment/suite number in a separate column?
do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?
and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?
address fields are NOTORIOUSLY the wrong example to use when discussing normalization
:)|||I always enjoy a goo d pedantic discussion...
Speaking of OS/390 z/OS
varchar is still painful in DB2 for the Client and/or COBOL Sprocs?
I'm about to launch a new dev project there and am in the middle of building the model soon...and they want free form description columns out the but at 300 bytes...
I need to talk them down to 255 to avoid LONG datatypes, but since they have so many, I was hoping to use varhcar.
I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...|||I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...
Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)
Joydeep|||Is there anybody who cares for Domain Integrity? I think there should be some specific norms for database designing and normalization.Then the fuss about char and varchar implementation should have been gone..;)
Joydeep
Really now.
At the moment, I just want to slam the damn thing into production and make the deadline.
If this were SQL Server I was working on, it wouldn't be a problem.
I think I'm gonna go with:
, COL1 CHAR(255) NOT NULL WITH DEFAULT
I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...
Ever seen an external COBOL Stored Procedure for DB2 OS/390?
And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...|||Really now.
At the moment, I just want to slam the damn thing into production and make the deadline.
If this were SQL Server I was working on, it wouldn't be a problem.
I think I'm gonna go with:
, COL1 CHAR(255) NOT NULL WITH DEFAULT
I want my developers to be happy...actually I want my developers to be productive and accurate, i.e. I don't want code blowing up all over the place...
Ever seen an external COBOL Stored Procedure for DB2 OS/390?
And they've implemented some heavy duty Changeman procedures...they can't even compile code unless it's in a package...
I agree with you for the above case.But don't you think database designing should involve a greater time than the rest of the jobs in production? Meeting deadlines is always a headache,but do you think we could sacrifice the dedicated time of designing for the sake of deadline only? :)
Joydeep|||do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'I'd do it in a second if it didn't place an undo burden on the person entering the data, and if there were a simple method of ensuring data entry integrity. Many business processes (such as bulk mail discounts) require the address to be parsed and sorted in a specific manner.
do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?I would absolutely do that if I had additional zip code attributes to store, such as demographics. Heck, I might do it just to ensure the validity of the zip codes that are entered. Yeah, it IS a one-to-many relationship, whether you choose to materialize the data or not.
and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?Yes, for stricty relationtional integrity zip4 codes should be a subtable of zip, and only the foreign key to zip4 should be stored in the adress table. But most applications do not require and cannot efficiently enforce these rules on the users. Asking the user to separte ZIP from ZIP4, however, is a pretty small request, and greatly facilitates grouping and sorting by zip code when zip4 is not required.
address fields are NOTORIOUSLY the wrong example to use when discussing normalizationAu contraire! The notorious unreliability of address fields make them a great cautionary tale against storing multiple attributes in a single column.|||Well, let's see.
The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.
I'm ok with that, and I've already made modifications to tha model. It's pretty sound, but there are some definete kluges in there.
Also, I have the management team to blame.
I will still be worrying about performance and data integrity...but the integrity might take a hit in some places...
I'm gonna start a new thread|||Well, let's see.
The business hired a management team to develop the specs. I have been going through them and we are working out the kinks. The requirements are what they are, but the business keeps saying they don't know what they want specifically.
I'm ok with that, and I've already made modifications to the model. It's pretty sound, but there are some definete kluges in there.
Also, I have the management team to blame.
I'm gonna start a new thread
Yea, thats a common problem.They don't even clear up the functional areas properly.That makes the case complicated.Too many groups spoil the broth.
I think you need more patience than technical expertise here...:D
Good luck!!
Joydeep|||Trick is to not care so much
