Teradata Table Naming

Database
Enthusiast

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?
Tags (1)
23 REPLIES
Enthusiast

Re: Teradata Table Naming

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

Re: Teradata Table Naming

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

Re: Teradata Table Naming

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

Re: Teradata Table Naming

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

Senior Apprentice

Re: Teradata Table Naming

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

Dieter

Re: Teradata Table Naming

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.

Senior Apprentice

Re: Teradata Table Naming

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

Re: Teradata Table Naming

Hi Dieter,

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

cheers

Re: Teradata Table Naming

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.