VARCHAR fields

UDA
Enthusiast

VARCHAR fields

Sorry for the newbie quesiton, but in my early encounters with creating tables in Teradata I tried to keep VARCHAR sizes to a minimum. Meaning, if the data is not going to be more than 10 characters long from the source database, then I would use VARCHAR(10) in TD for the table create. I have had a consultant tell me that the TD standard is use VARCHAR(255) for any VARCHAR columns as it does not take any more space than anything less than that. What I am wondering is if there are any other concerns with this approach. To be it seems a little odd coming from the MS SQL side, but maybe this is the "standard" Thoughts?

ss
9 REPLIES
Teradata Employee

Re: VARCHAR fields

Hello,

I seriously doubt if it is a standard practice, and it can degrade the performance as well! If you know your data, I'll suggest you to use data-types accordingly.

Regards,

Adeel
Enthusiast

Re: VARCHAR fields

TD works in same way as other work with data type of varchar.
when we calculate table weight we do think in same way as other.
after insertion, I found the near by same size of table. which was calculated in design phase.

SN
Enthusiast

Re: VARCHAR fields

just to add to the comments here......any VARCHAR field consumes extra bytes (than actual data length) to hold the data length of the data.So, unless there is lot of varied data legth data coming in for a field, using a VARCHAR doesnt make much sense.

thx,
Enthusiast

Re: VARCHAR fields

Thank you all for your replies. I was hoping that is what you would say. It would drive me nuts to see a bunch of varchar(255)'s out there for a reason unknown by me.

ss
Enthusiast

Re: VARCHAR fields

VARCHAR clarification: 

We had recently converted our code from Oracle to Teradata.  Now it's time to do some optimization & have a question in regards to VARCHAR fields. 

We have several fields that are defined as VARCHAR(16000) when in reality the longest length is 14.  I've noticed that running some of our projects that we occasionally hit a spool space error because it's trying to carry all 16,000 bytes.  Is this true and also what are effects will this have on processing?

I don't know why it's defined at 16000 but like I said earlier we're going to start optimizing our code.

Junior Contributor

Re: VARCHAR fields

There was a recent thread on VARCHARs:

forums.teradata.com/forum/database/behavior-of-varchar-and-compressed-columns-in-spool

It's great when you actually start optimizing/thinking about your code.

I've seen projects where nobody cares about that, but afterwards everybody complains about bad performance. And yes, the origins are mainly O****e :-)

Dieter

Enthusiast

Re: VARCHAR fields

thanks dieter for the very useful info...

Just want to add one point here regarding the choice of char vs varchar that if the length of the string is

less than 10 chars then we sould use char and if the length of the string column is more  than 10 chars than

we should define it as varchar..

the reason is that varchar takes additonal 2 bytes so if we define varchmar(1), it may end up taking 1+2 =3 bytes, however original length is only 1 bytes.

Please correct me if i am wrong or add something here..

cheers!

Nishant

Junior Supporter

Re: VARCHAR fields

Hi,

I have a design question with respect to varchars.

1. I beleieve that the max size that can be defined for varchar is 64000 for server character set latin. I have 2-3 fields in my table that will have datatype as varchar(2500) and varchar(1500). How many bytes does a varchar(2500) occupy - my understanding is 2500+2. Is there any concept that if the size is defined more than a x value e.g varchar(x), the varchar occupies some number of bytes ? I read the manuls for varchar datatype, but it doesn't mention any such thing. Hence, the question.

2. What is the max size of a row that a table can have ? I beleieve it is 64K bytes. Do we need to consider any other thing or this is applicable for all TD installstions. We are on TD 14. we have a very large table coming to our platform.

--Samir

Teradata Employee

Re: VARCHAR fields

You will want to read the Database-level Capacity Planning Considerations chapter (14) of the Database Design reference. It will answer all of these questions and more.

-Dave