17.10 - INDEX, secondary index definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

A keyword used to define secondary indexes for the table. The INDEX list is an extension to ANSI SQL.

UNIQUE
The named column must be unique.
The primary index and any secondary indexes can be defined to be unique. The only exception is if a queue table has only a QITS column. In this case, the QITS column must also be the primary index, so it cannot be a UPI. This is because timestamp values can repeat and therefore cannot be assumed to be unique (see Teradata Vantage™ - Data Types and Literals, B035-1143).
index_name
Optional name for the index.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
index_column_name
A column in the column set whose values are to be used as the basis for a secondary index.
Columns in the list cannot have a BLOB, CLOB, Period, XML, Geospatial, JSON, or DATASET data type.
If you specify more than one column name, the index is created on the combined values of each column named. A maximum of 64 columns can be specified for an index, and a combined maximum of 32 secondary, hash, and join indexes can be created for one table (a multicolumn NUSI defined with an ORDER BY clause counts as two indexes in this calculation). This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints for nontemporal tables and the single-table join indexes used to implement PRIMARY KEY and UNIQUE constraints for temporal tables.