All Forums UDA
ssteckley 10 posts Joined 07/08
28 Jul 2008
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

Adeel Chaudhry 773 posts Joined 04/08
28 Jul 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

arshad 13 posts Joined 12/07
29 Jul 2008

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 77 posts Joined 01/07
29 Jul 2008

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,

ssteckley 10 posts Joined 07/08
29 Jul 2008

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

spidermonk 9 posts Joined 11/12
11 Jul 2013

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.

dnoeth 4628 posts Joined 11/04
11 Jul 2013

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

Dieter

11 Jul 2013

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
 
 

teradatauser2 236 posts Joined 04/12
06 Jun 2015

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

david.craig 73 posts Joined 05/13
08 Jun 2015

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

You must sign in to leave a comment.