SQL Operations on Queue Tables | CREATE TABLE | Teradata Vantage - 17.10 - SQL Operations on Queue Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

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

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 two 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

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) and browse mode rows use the standard SQL ordering mechanisms such as an ORDER BY clause.

Vantage 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. You cannot use consume mode SELECT statements in ANSI session mode, because all ANSI mode cursors are, by default, positioned cursors. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 and Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446 for further information.

Vantage 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 statements frequently on a queue table because these operations spoil the FIFO cache for the entries for that table. As a result, Vantage must perform a full-table scan the next time it accesses the FIFO cache to rebuild it.

Instead, you should reserve these statements for exception handling.

See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 and Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 for more detailed information about using these statements with queue tables.

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