What is queue table in Teradata?

Queue Tables feature simplifies the implementation of asynchronous, event-driven applications. Performance is improved by saving CPU and network resources.Queue tables possess all the properties
of regular Teradata Database tables:persistence, scalability, parallelism, ease of use, reliability, availability  recovery, and security. In addition, queue tables possess FIFO queue properties such as push, pop, and peek queue operations.

 These properties allow queue tables to provide flexibility,power, functionality, and leverage the natural performance characteristic of the Teradata Database. With queue tables, the Teradata Database
provides the capability to run real-time,event-driven active data warehouse activity while still having access to historical data providing the capability to make more effective business decisions in realtime
processing environments.


The queue table definition is different from a standard base table in that a queue table always contains a user-defined insertion timestamp (QITS) as the first column of the table. The QITS contains the time the row was inserted into the queue table as the means for approximate FIFO ordering.

Queue properties are implemented as following.

1. The FIFO push operation is defined as a SQL INSERT operation to store rows into a queue table.
2. The FIFO peek operation is defined as an SQL SELECT operation to retrieve rows from a queue table without deleting them. This is also referred to as browse mode.
3. The FIFO pop operation is defined as an SQL SELECT AND CONSUME operation to retrieve a row from a queue table and delete that selected row upon completion of the read. This is also referred to as consume mode.A consume mode request goes into a delayed state when a SELECT AND CONSUME finds no rows in the queue table. The request remains idle until an INSERT to that queue table awakens the request; that row is returned, and then it’s deleted. Consumed rows are returned in FIFO order.

create queue table syntax :

create table prod.myquetable, queue

(

create_timestamp timestamp(6) not null default current_timestamp(6),

uid int, 

uname int

) ;