Archives of the TeradataForum

Message Posted: Tue, 04 Feb 2003 @ 16:12:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Krishnan:

This is an area where a lot of people get a bit confused. The biggest (not only) factor in deciding whether to use CHAR vs VARCHAR is the data being stored. The biggest potential advantage for VARCHAR is that it can make the majority of the rows shorter. When the rows are shorter, more rows can go into a single block. The more rows in a single block, the fewer the needed number of blocks. The fewer the number of blocks, the fewer the number of I/O operations to read the data - hence, faster processing. A side benefit is more relative storage capacity for other rows or tables.

Now, with that being said, VARCHAR can take up more storage if you do not consider the physical characteristics of YOUR data. This due to the fact that Teradata must store a "field" along with the data when using VARCHAR. It contains the number of bytes stored in the column for the particular row. So, if you declare a VARCHAR(20) and all or most of your data is 20 characters long you are wasting space (20+2 bytes to store the value of 20 for the length = 22 bytes instead of 20) Therefore, if you have 1M rows, you waste 2M bytes @ 2 bytes per row.

VARCHAR makes the most sense when a small percent of your data is the maximum length and most of your data is MORE than 2 bytes less than the maximum length.

I hope this readers digest version of this consideration makes sense. If not, feel free to contact me directly. I am also pretty sure that you are going to receive a variety of other considerations from other subscribers. Once you take them all into account, you can make an educated decision for YOUR data.

Regards,

Mike

	<Prev	Next>		<<First	<Prev	Next>	Last>>