Database Limits | Teradata Vantage - Database Limits - Advanced SQL Engine - Teradata Database

Maximum name size for database objects, for example, account, attribute, authorization, column, constraint, database, function, GLOP, index, macro, method, parameter, password, plan directive, procedure, profile, proxy user, query, role, procedure, table, transform group, trigger, UDF, UDM, UDT, using variable name, view.

See Teradata Vantage™ - SQL Fundamentals, B035-1141 for other applicable naming rules.

Maximum system name size.

Used in SQL statements for target level emulation. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

Maximum number of columns per error table.

This limit includes 2,048 data table columns plus 13 error table columns.

Maximum number of UDT columns per base data table.

The same limit is true for both distinct and structured UDTs.

The absolute limit is 2,048, and the realizable number varies as a function of the number of other features declared for a table that occupy table header space.

Maximum number of LOB and XML columns per base data table.

This limit includes predefined type LOB columns and UDT LOB columns.

A LOB UDT or XML UDT column counts as one column even if the UDT is a structured type that has multiple LOB attributes.

Maximum number of bytes per table header per AMP.

A table header that is large enough to require more than ~64,000 bytes uses multiple 64KB rows per AMP up to 1 MB.

A table header that requires 64,000 or fewer bytes uses only a single row and does not use the additional rows that are required to contain a larger table header.

The maximum size for a table header is 1 MB.

Maximum logical row size.

In this case, a logical row is defined as a base table row plus the sum of the bytes stored in a LOB or XML subtable for that row.

Maximum non-LOB column size for a nonpartitioned table.

This limit is based on subtracting the minimum row overhead value for a nonpartitioned table row (12 bytes) from the system-defined maximum row length (64,256 bytes).

Maximum non-LOB column size for a partitioned table.

This limit is based on subtracting the minimum row overhead value for a partitioned table row (16 bytes) from the system-defined maximum row length (64,256 bytes).

Maximum amount of data per column that can be multivalue compressed for GRAPHIC, LATIN, KanjiSJIS, and UNICODE server character sets.

Maximum number of columns that can be compressed per primary-indexed or primary-AMP-indexed table or join index using multivalue compression or algorithmic compression.

This assumes that the object is not a NoPI table or join index or a global temporary trace table. All other tables, hash indexes, and join indexes must have a primary index or a primary AMP index. Primary indexes and primary AMP indexes cannot be either multivalue compressed or algorithmically compressed. Because of this, the limit is the maximum number of columns that can be defined for a table, which is 2,048, minus 1.

The limit for multivalue compression is far more likely to be reached because of table header overflow, but the amount of table header space that is available for multivalue compressed values is limited by a number of different factors.

Join index columns inherit their compression characteristics from their parent tables.

Maximum number of columns that can be compressed per NoPI table using multivalue compression or algorithmic compression Column-partitioned NoPI join index columns inherit their compression characteristics from their parent tables.

Maximum width of data that can be multivalue compressed for BYTE, BYTEINT, CHARACTER, GRAPHIC, VARCHAR, and VARGRAPHIC data types.

Maximum width of data that can be multivalue compressed for data types other than BYTE, BYTEINT, CHARACTER, DATE, GRAPHIC, VARCHAR, and VARGRAPHIC.

Maximum width of data that can be algorithmically compressed.

The maximum data width is unlimited if you specify only algorithmic compression for a column.

If you specify a mix of multivalue and algorithmic compression for a column, then the limits for multivalue compression also apply for algorithmic compression.

Maximum number of primary indexes or primary AMP indexes per table, hash index, or join index that is not a NoPI database object.

Minimum number of primary indexes per primary-indexed table, hash index, or join index.

Minimum number of primary AMP indexes per primary-AMP-indexed table or join index.

Maximum number of primary indexes per primary-AMP-indexed or NoPI or join index or global temporary trace table.

Maximum number of column partitions per table, including two columns partitions reserved for internal use).

Minimum number of column partition numbers that must be available for use by an ALTER TABLE request to alter a column partition.

Maximum combined partition number for a single-level column-partitioned table or column-partitioned join index.

Maximum combined partition number for a multilevel partitioning for 2-byte partitioning.

Maximum combined partition number for a multilevel partitioning join index for 8-byte partitioning.

Maximum number of ranges, excluding the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN and UNKNOWN partitions, for a RANGE_N partitioning expression for 2-byte partitioning.

This value is limited by the largest possible INTEGER value.

Maximum number of ranges, excluding the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN and UNKNOWN partitions, for a RANGE_N partitioning expression for 8-byte partitioning.

This value is limited by the largest possible BIGINT value.

Maximum number of partitions, including the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN partitions, for a single-level partitioning expression composed of a single RANGE_N function with INTEGER data type.

Maximum number of ranges for a single-level partitioning expression composed of a single RANGE_N function with INTEGER data type that is used as a partitioning expression if the NO RANGE and UNKNOWN partitions are not specified.

Maximum number of ranges for a single-level partitioning expression composed of a single RANGE_N function with BIGINT data type that is used as a partitioning expression if the NO RANGE and UNKNOWN partitions are not specified.

Maximum number of ranges for a single-level partitioning expression composed of a single RANGE_N function with BIGINT data type that is used as a partitioning expression if both the NO RANGE and UNKNOWN partitions are specified.

Maximum value for a partitioning expression that is not based on a RANGE_N or CASE_N function.

This is allowed only for single-level partitioning.

Maximum number of defined partitions for a row partitioning level if the row partitions specify the RANGE_N or CASE_N function.

Maximum number of defined partitions for a row partitioning level if the row partitions do not specify the RANGE_N or CASE_N function.

Maximum number of partitions for a partitioning level when you specify an ADD clause.

This value is computed by adding the number of defined partitions for the level plus the value of the integer constant specified in the ADD clause.

Maximum number of partitions for a column partitioning level when you do not specify an ADD clause and at least one row partitioning level does not specify an ADD clause.

Maximum number of column partitions for a column partitioning level when you do not specify an ADD clause, you also specify row partitioning, and each of the row partitions specifies an ADD clause.

Maximum number of partitions for each row partitioning level without an ADD clause in level order, if using the number of row partitions defined as the maximum for this and any lower row partitioning level without an ADD clause.

Maximum number of partitions for a CASE_N partitioning expression.

This value is limited by the largest possible INTEGER value.

Maximum value for a RANGE_N function with a BIGINT data type that is part of a partitioning expression.

Maximum number of partitioning levels for 2-byte partitioning.

Other limits can further restrict the number of levels for a specific partitioning.

Maximum number of partitioning levels for 8-byte partitioning.

Other limits can further restrict the number of levels for a specific partitioning.

Minimum number of partitions per row-partitioning level for a multilevel partitioning primary index.

Maximum number of partition number ranges from each level that are not eliminated for static row partition elimination for an 8-byte row-partitioned table or join index.

Maximum number of columns per foreign and parent keys in a referential integrity relationship.

Maximum number of row-level security statement-action UDFs that can be defined per table.

Maximum number of non-set row-level security constraint encodings that can be defined per constraint.

The valid range is from 1 to 10,000.

0 is not a valid non-set constraint encoding.

Maximum number of LOB rows per rowkey per AMP for NoPI LOB or XML tables

Maximum structured UDT size.

This value is based on a table having a 1 byte (BYTEINT) primary index. Because a UDT column cannot be part of any index definition, there must be at least one non-UDT column in the table for its primary index.

Row header overhead consumes 14 bytes in an NPPI table and 16 bytes in a PPI table, so the maximum structured UDT size is derived by subtracting 15 bytes (for an NPPI table) or 17 bytes (for a PPI table) from the row maximum of 64,256 bytes.

Maximum number of UDT columns per base data table.

The absolute limit is 2,048, and the realizable number varies as a function of the number of other features declared for a table that occupy table header space.

The figure of 1,600 UDT columns assumes a FAT table header.

This limit is true whether the UDT is a distinct or a structured type.

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

Other rules apply for Japanese character sets, which might restrict names to fewer than 30 bytes. See SQL Fundamentals for the applicable rules.

Maximum number of attributes that can be specified for a structured UDT per CREATE TYPE or ALTER TYPE request.

The maximum is platform-dependent, not absolute.

Maximum number of attributes that can be defined for a structured UDT.

While you can specify no more than 300 to 512 attributes for a structured UDT per CREATE TYPE or ALTER TYPE request, you can submit any number of ALTER TYPE requests with the ADD ATTRIBUTE option specified as necessary to add additional attributes to the type up to the upper limit of approximately 4,000.

Maximum number of levels of nesting of attributes that can be specified for a structured UDT.

Maximum number of methods associated with a UDT.

There is no absolute limit on the number of methods that can be associated with a given UDT.

Methods can have a variable number of parameters, and the number of parameters directly affects the limit, which is due to Parser memory restrictions.

There is a workaround for this issue. See “ALTER TYPE” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for details.

Maximum number of input parameters with a UDT data type of VARIANT_TYPE that can be declared for a UDF definition.

Minimum number of dimensions that can be specified for a multidimensional ARRAY or VARRAY data type.

Maximum number of columns returned by a dynamic result table function.

The valid range is from 1 to 2,048. There is no default.

Maximum length of a dynamic SQL request in a procedure.

This includes its SQL text, the USING data (if any), and the CLIv2 parcel overhead.

Maximum number of parameters specified in a UDF defined without dynamic UDT parameters.

Maximum number of parameters that can be defined for a constructor method for all types except ARRAY/VARRAY

Maximum number of parameters that can be defined for a constructor method of an ARRAY/VARRAY type

Maximum number of combined return values and local variables that can be declared in a single UDF.

Maximum number of combined external routine return values and local variables that can be instantiated at the same time per session.

Maximum number of parameters specified in a UDF defined with dynamic UDT parameters.

The valid range is from 0 to 15. The default is 0.

Maximum number of parameters specified in an external procedure written in C or C++.

Maximum size of an ARRAY or VARRAY UDT.

This limit does not include the number of bytes used by the row header and the primary index or primary AMP index of a table.

Maximum length of external name string for an external routine.

An external routine is the portion of a UDF, external procedure, or method that is written in C, C++, or Java (only external procedures can be written in Java). This is the code that defines the semantics for the UDF, procedure, or method.

Maximum number of Statement Areas per SQL procedure diagnostics area. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 and Teradata Vantage™ - SQL External Routine Programming , B035-1147 .

Maximum size of the Index Wizard workload cache.

The default is 48 MB and the minimum is 32 MB.

Maximum number of columns and indexes on which statistics can be collected or recollected at one time.

512 or limited by available parse tree memory and amount of spool.

Maximum number of pseudo indexes on which multicolumn statistics can be collected and maintained at one time.

A pseudo index is a file structure that allows you to collect statistics on a composite, or multicolumn, column set in the same way you collect statistics on a composite index.

This limit is independent of the number of indexes on which statistics can be collected and maintained.

Maximum number of sets of multicolumn statistics that can be collected on a table or join index if single-column PARTITION statistics are not collected on the table or index.

Maximum number of sets of multicolumn statistics that can be collected on a table or join index if single-column PARTITION statistics are collected on the table or index.

Maximum number of secondary, hash, and join indexes, in any combination, per base data table.

Each composite NUSI defined with an ORDER BY clause counts as 2 consecutive indexes in this calculation.

The number of system-defined secondary and single-table join indexes contributed by PRIMARY KEY and UNIQUE constraints counts against the combined limit of 32 secondary, hash, and join indexes per base data table.

Maximum number of columns referenced in the fixed part of a compressed join index.

Vantage implements a variety of different types of user-visible compression in the system.

When describing compression of hash and join indexes, compression refers to a logical row compression in which multiple sets of nonrepeating column values are appended to a single set of repeating column values. This allows the system to store the repeating value set only once, while any nonrepeating column values are stored as logical segmental extensions of the base repeating set.

Maximum SQL text size per request.

This includes SQL request text, USING data, and parcel overhead.

Maximum number of entries in an IN list.

There is no fixed limit on the number of entries in an IN list; however, other limits such as the maximum SQL text size, place a request-specific upper bound on this number.

Maximum number of tables and single-table views that can be joined per query block.

This limit is controlled by the MaxJoinTables DBS Control field.

Maximum number of subquery nesting levels per query.

Maximum number of tables or single-table views that can be referenced per subquery.

This limit is controlled by the MaxJoinTables DBS Control field..

Maximum number of fields in a USING row descriptor.

Maximum number of open cursors per embedded SQL program.

Maximum SQL text response size.

Maximum number of columns per DML request ORDER BY clause.

Maximum number of columns per DML request GROUP BY clause.

Maximum number of row-level security constraints per table.

Maximum number of hierarchical row-level security constraints per user or profile.

Maximum number of values per hierarchical row-level security constraint.

Maximum number of non-hierarchical row-level security constraints per user or profile.