Deterministic Characteristics Clause - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

This optional clause declares whether the function returns the same results for identical inputs or not.

Teradata Database supports two deterministic characteristics options:

  • DETERMINISTIC
  • NOT DETERMINISTIC

Teradata Database must evaluate each row selected by a nondeterministic UDF condition individually because the evaluation of the function can change for each row selected within a query. This qualification includes both specifying NOT DETERMINISTIC explicitly and not specifying DETERMINISTIC, because NOT DETERMINISTIC is the default option for the clause.

A predicate that includes a nondeterministic UDF specified on an index results in an all-AMP operation in spite of the index having been specified in the predicate.