Teradata: ANSI Mode vs Teradata(BTET) Mode

Teradata: ANSI Mode vs Teradata(BTET) Mode

Note: Same SQL statement may perform differently in each mode.



How to change Teradata session mode Note: Above statement must be issued before Logon otherwise command will fail with below error.
*** Error: You must not be logged on to change the SQLFLAG or TRANSACTION settings



Difference between ANSI mode & Teradata mode based on various parameters


Ansi Mode

Teradata Mode

Transactions / SQLs

Explicit COMMIT statement is required to persist completed DMLs/transactions in database

DMLs/transactions are autocommit implicitly

Default Table creation

MULTSET table will be created if not specified explicitly

SET table will be created if not specified explicitly

Default Character Comparison

Data comparison is CASESPECIFIC

Data comparison is not CASESPECIFIC

Logoff

Completed DMLs/transactions will not persist in database if logged off without commit

Completed DMLs/transactions generally persist in database after logoff.
But if statements are executed within BT and ET was not issued after the transactions then data will not persist in database after logoff

Character Truncation

Normal truncation using CAST function will result in error.
Example: select cast('Teradata Database' as varchar(10));
Result: *** Error 3996 Right truncation of string data.

Normal truncation using CAST function will give truncated result with the specified length.
Example: select cast('Teradata Database' as varchar(10));
Result: Teradata D

Statements after DDL

COMMIT must be the next statement after any DDL statement else statements will fail

Generally any statement can be executed after DDL. But if DDL statement is executed within BT then ET must be next statement else statements will fail.

BT(Begin Transaction) / ET(End Transaction)

BT is not permitted

BT can be used to stop implicitly committing every statement, once ET is issued then it will commit all the transactions.