15.00 - SQL Operations on Queue Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

SQL Operations on Queue Tables

SQL interfaces provide support for FIFO push, pop, and peek operations on queue tables as indicated in the following table.

 

THIS FIFO operation …

Is supported by this SQL statement …

push

INSERT.

pop

SELECT AND CONSUME TOP 1.

peek

SELECT.

The different operative modes performed by the 2 forms of the SELECT statement are referred to as consume and browse modes, respectively. Note that you cannot use either the PERCENT or WITH TIES options with consume mode SELECT operations on a queue table. Additionally, you must always specify 1, and only 1, as the value for n in the TOP n specification. See SQL Data Manipulation Language for details.

 

This form of SELECT …

Performs this function on a queue table …

SELECT AND CONSUME TOP 1

consume.

SELECT

browse.

Note that consume mode rows are FIFO‑ordered unless otherwise explicitly ordered (see “Ordering Queue Table Rows” on page 689) and browse mode rows use the standard SQL ordering mechanisms such as an ORDER BY clause.

Teradata Database supports the following DML statements for embedded SQL processing of queue tables.

  • DELETE
  • INSERT
  • MERGE
  • SELECT
  • SELECT AND CONSUME
  • UPDATE
  • Note that SELECT AND CONSUME is not supported for positioned cursors. This means that you cannot use consume mode SELECT requests in ANSI session mode, because all ANSI mode cursors are, by default, positioned cursors. See SQL Stored Procedures and Embedded SQL and Teradata Preprocessor2 for Embedded SQL Programmer Guide for further information.

    Teradata Database provides queue table DML support for all the following forms of embedded SQL.

  • Static
  • Dynamic
  • Static cursor
  • Dynamic cursor
  • As a general rule, you should not perform DELETE, MERGE, and UPDATE requests frequently on a queue table because these operations spoil the FIFO cache for the entries for that table. As a result, Teradata Database must perform a full‑table scan the next time it accesses the FIFO cache to rebuild it.

    Instead, you should reserve these requests for exception handling.

    See SQL Data Manipulation Language and SQL Stored Procedures and Embedded SQL for more detailed information about using these requests with queue tables.

    Because queue tables are persistent database objects in the Teradata Database, the basic Teradata features for transactions are provided for them. Like other Teradata Database transactions, queue table transactions have the ACID properties of atomicity, consistency, isolation, and durability. See SQL Request and Transaction Processing for further information about Teradata transactions.