Teradata: ANSI Mode vs Teradata(BTET) Mode

Pradeep

Teradata: ANSI Mode vs Teradata(BTET) Mode

ANSI Session Mode
- ANSI session mode is a teradata session where SQL statements will adhere to the rules defined by the ANSI SQL specification.
- COMMIT must be the next statement after any DDL statement in ANSI mode, otherwise those statement will fail with below error.
```
*** Error 3722 Only a COMMIT WORK or null statement is legal after a DDL Statement.
```
- Watch below video on youtube to see how to set ANSI session mode.
Teradata Session Mode
- Teradata session mode (also known as BTET session mode) is a teradata session where SQL statements follow rules that are defined by Teradata.
- Teradata session mode provides a way to process transactions by legacy applications.
- 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 with below error.
```
*** Failure 3932 Only an ET or null statement is legal after a DDL Statement.
```
- Watch below video on youtube to see how to set BTET session mode.

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

How to change Teradata session mode

Change Mode to ANSI Mode
```
.SET SESSION TRANSACTION ANSI
```
Change Mode to Teradata Mode
```
.SET SESSION TRANSACTION BTET
```

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.