All Forums Database
Kishore_1 208 posts Joined 03/10
22 Mar 2011
Teradata Table Naming

Why is that teradata limits the size of a table name or index name defined on it
to be 30 characters at maximum?
Is there any specific reason behind this?

teradatatester 69 posts Joined 01/10
28 Mar 2011

Is there an admin setting that can be changed to increase the size limit for a table name?

dnoeth 4628 posts Joined 11/04
28 Mar 2011

The maximum length of a name was 18 according to old Standard SQL, so 30 was much more than that.
The system tables are already prepared for VARCHAR(128), so a future release will change this limitation. But it's not a high priority project, because 30 is usually enough.

And i don't want to key in a table name like the_table_name_i_couldnt_use_in_older_releases_although_i_always_wanted_but_now_i_can ;-)

Dieter

Dieter

Alexander Davis 1 post Joined 05/11
08 May 2011

Dieter,

The fact that an RDBMS allows you to use more than 30 characters does not mean that you are forced to always use long names, right? That would be a wrong assumption.

The problem for us is that Teradata is the most restrictive among all RDBMSs we use (and I think we use all of them), and since it's the lowest common denominator, our internal data modeling standards force all data object names (table names, column names) in all systems (Teradata, MS SQL Server, Oracle, etc) to use Teradata limitations. We have entities which are quite long (these can be multi-word names, etc), plus we use prefixes for things like triggers, indexes, etc, so we only allow 23 characters for table names. You may think that this is long enough, but in practice, it's not. Now, to conform to Teradata limitations, we use weird abbreviation rules and make up names that are incomprehensible (e.g. how do you like mgmt_dlv_aggr_set_asc_hist?).

And with entity frameworks and other data access technologies, these names leak from our database code to C/C#/JavaScript, etc, so we need to write extra code to convert them to more readable class/property names. We have a special process for making abbreviations, a database and web site listing abbreviations, and rules, etc. These hassles affect not only Teradata developers but developers, who don't even know what Teradata is, all simply because Teradata does not allow longer names. Maybe you do not see this as a priority, but it's a huge problem for a lot of people at our corporation (a bigger semiconductor maker). If Teradata supported longer names it would've made a lot of people (DBAs, architects, developers) more productive, efficient, and happy.

Best regards,

Alek

darcyzam 3 posts Joined 06/11
25 Aug 2011

Hi Dieter,

I was just wondering if you happen to know where can we find a documentation about Teradata known limits (length of variables, tablename, view, triggers, etc)?

Appreciate to hear from you soon.

cheers

darcy

dnoeth 4628 posts Joined 11/04
26 Aug 2011

Hi Darcy,
there's an appendix "Teradata System Limits" in the Database Design manual listing various limitations.

Dieter

Dieter

darcyzam 3 posts Joined 06/11
07 Sep 2011

Hi Dieter,

Many thanks! I've  found it.

however, i could not find a maximum limit on the length of a column name in a table and or other db objects for that matter. Do you happen to know this specific details or better if we do have available documentation for these information.

Thanks again for the response and looking forward to hear from you soon.

cheers...

darcy

APPENDIX B Teradata System Limits

This appendix provides the following Teradata Database limits:

• System limits

• Database limits

• Session limits

The appendix also provides a list of the data types for the various system-derived and

system-generated columns.

The reported limits apply only to the platform software. Platform-dependent client limits are

not documented in this appendix.

dnoeth 4628 posts Joined 11/04
08 Sep 2011

Hi Darcy,
it's there, you just have to find it :-)

Database Limits -> Table and View Limits:

"Maximum database, user, base table, view, macro, index, trigger,
stored procedure, UDF, UDM, UDT, replication group name,
constraint, or column name size."

"30 bytes in Latin or Kanji1 internal representationl"

More details are in the "SQL Fundamentals",
Chapter 2: Basic SQL Syntax and Lexicon -> Names

Dieter

Dieter

darcyzam 3 posts Joined 06/11
12 Sep 2011

Hi Dieter,

Okay. thanks for the info. will keep ploughing to the documentations.

cheers

dmuraco 3 posts Joined 01/11
15 Jan 2013

I agree with Alexander Davis.
The limitations cause extra non-value add work and they should be changed.
Dieter, thanks for the information posted.  However, it seems strange that compatability with other major vendors and alignment with defacto standards supporting long columns name isn't a TD priority.
The more TD positions itself in the marketplace as a warehouse platform for Oracle OLTP apps, the more important aligning with other vendors limitations will become.
 
 

ToddAWalter 316 posts Joined 10/11
16 Jan 2013

It is currently intended that Teradata 14.10 due out later this year will increase this limit. As always, this is not a promise, only the official release content documentation can describe the actual content of the release. But this item is actively being engineered.

malli 10 posts Joined 08/12
01 Aug 2014

TERADTA VERSION :14.10.02.10 is allowing to create a table with more than 30 characters because the tvmfield in tvm table defined with varchar(128) .
But dbc.tables whill show only first 30 chars due to casting in their ddl and the remaining chars will be trimmed.
 
Thanks
Malli

david.craig 73 posts Joined 05/13
01 Aug 2014

The dbc.tables view is a legacy view which truncates names at 30 characters for compatibility. It sounds like you would prefer the modern views which end in 'V' (e.g., dbc.tablesV).
The Extended Object Names feature of Teradata 14.10, and features going back to 12.0, provide these capabilities. Refer to the SQL Fundamantals and Data Dictonary reference for details.
There also is an Orange Book on the subject is you have access to those.

dnoeth 4628 posts Joined 11/04
02 Aug 2014

Btw, the "lowest common denominator" is not Teradata, even Oracle 12c still supports only 30 characters for object names and 8 characters for database names :-)

Dieter

david.craig 73 posts Joined 05/13
04 Aug 2014

Other factors which impact object name lengths in a database are:
- the repertoire (i.e., script) of the characters,
- the Unicode encoding used to enforce the naming rules,
- rules customization.
Oracle and IBM DB2 use UTF8 which requires at least 3 bytes to encode Asian characters in names, 2 bytes for European characters, and 1 byte for basic Latin (i.e., ASCII). Teradata uses UTF16 which requires 2 bytes after NFD normalization.  Teradata also has a user-defined name validation rule capability.
 

rnekkanti 12 posts Joined 05/15
04 Mar 2016

Hi Guys,
I see that my database(ver:15.00.04.03) allows table names > 30 (I think 128 char limit is in place in our envronment) . But i still see the ET,LT,WT tables created during MLOAD still confine to 30 character limit . Is there a separate limit for these utility tables created at runtime ?. 
 
I am not able to find any specific limits in the documentation. Can somebody let me know if there is a limit on these utility tables ?
 
 

Thanks,
RN

david.craig 73 posts Joined 05/13
05 Mar 2016

There is no separate limit for load utility table names. How are you determining that there is a limitation?

Adeel Chaudhry 773 posts Joined 04/08
06 Mar 2016

Multi/Fast-Load are obsolete now. Can you try with TPT which is a replacement for them?

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

plentyfish 16 posts Joined 05/10
21 Jun 2016

Hi all, whats the SQL to find list of table names longer than 30 characters?

tomnolan 594 posts Joined 01/08
21 Jun 2016
select databasename,tablename from dbc.tablesv where characters(tablename) > 30;

 

plentyfish 16 posts Joined 05/10
21 Jun 2016

Thanks Tom. I also just found the "length" function usefull about doing a bit of googling.
sel * from dbc.tablesv where length(tablename) > 30

piyush.lohana 4 posts Joined 09/14
01 Jul 2016

What happens if I have some tables in the database which have names more than, lets say 30 chars long, and NameValidationRule now is set to 1? Do I have to first rename those tables?

Piyush

Fred 1096 posts Joined 08/04
01 Jul 2016

No, existing objects are not affected. The new Name Validation rule applies when you CREATE new objects (or for the new name specified on a RENAME).

grommel 4 posts Joined 09/09
08 Jul 2016

MultiLoad and FastLoad aren't exactly obsolete -- they are still present in recent releases of TTU -- but I agree that you should use TPT nowadays. The MultiLoad and FastLoad partitions within the database are not obsolete, of course; they are used by TPT.
 
 

You must sign in to leave a comment.