Example: Creating a Queue with a UPI and a NUSI - Teradata Vantage

This example creates a queue table with a UPI defined on the queue sequence number identity column, QSN, and a NUSI defined on the queue insertion timestamp, QITS:

    CREATE TABLE qtbl_5, QUEUE (
      qits  TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      qsn   INTEGER GENERATED ALWAYS AS IDENTITY (NO CYCLE),
      col_3 INTEGER)
    UNIQUE PRIMARY INDEX (qsn)
    INDEX qits;

With a high rate of consumption of queue table rows, you typically replace the NO CYCLE option with CYCLE. CYCLE has the advantage of enabling the reuse of QSN numbers while not failing when MAXVALUE is exceeded. The UPI on QSN guarantee that the value of QSN is unique.