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.
- 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.
- 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.