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.
There was a recent thread on VARCHARs:
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 :-)
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..
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.
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.