17.00 - INDEX, secondary index definition using an ORDER BY clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

A keyword to introduce a secondary index definition using an ORDER BY clause to order the index on a single column. The INDEX list is an extension to ANSI SQL.

Unlike the indexes created by the UNIQUE and PRIMARY KEY constraint definitions, indexes defined by the index list can include nullable fields.

index_column_name
A column in the column set whose values are to be used as the basis for a primary or 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 non-temporal tables and the single-table join indexes used to implement PRIMARY KEY and UNIQUE constraints for temporal tables.