Case Sensitivity in Teradata

Case sensitivity of columns:

Tables can be created in ANSI mode or Teradata mode.

• We know that Teradata mode is case insensitive , so the character columns by default are defined as NOT CASESPECIFIC.

The data is stored in the case entered, but while making comparisons case is ignored.

.ie 'abc' is same as 'ABC'

• We know that ANSI mode is case sensitive, so the character columns by default are defined as CASE SPECIFIC.

The data will be stored and retrieved in same case entered. However comparisons are case sensitive.

.ie 'ABC' is not same as 'abc'

• We can set the session transaction using

SET SESSION TRANSACTION ANSI;

Or

SET SESSION TRANSACTION BTET;

• If we are in ANSI mode and wish to make case insensitive comparisons we can use the functions UPPER and LOWER to both side of the comparisons.

Ex:

Select * from employee where UPPER(employee_name)=UPPER(manager_name);

OR

Select * from employee where LOWER(employee_name)=LOWER(manager_name);

• When in ANSI mode we can enforce no case specific comparisons by making use of 'NOT CASESPECIFIC' attribute to the column in parenthesis following the column name.

SELECT * from employee where employee_name(NOT CASESPECIFIC)=manager_name(NOT CASESPECIFIC);

We can also abbreviate it as 'NOT CS'

SELECT * from employee where employee_name(NOT CS)=manager_name(NOT CS);

• When in BTET mode we can enforce case specific comparisons by making use of 'CASESPECIFIC' attribute to the column in  parenthesis following the column name.

SELECT * from employee where employee_name(CASESPECIFIC)=manager_name(CASESPECIFIC);

We can also abbreviate it as 'CS'

DDL - Part 2 - Column Level attributes

COLUMN LEVEL ATTRIBUTES:

Most of the column level attributes are Teradata extensions.

ANSI has very few column level attributes.

ANSI:

NOT NULL	--> Disallows NULL in the column.
DEFAULT user	--> Uses user id as default value.
DEFAULT value	--> Uses default value is input value is not provided.
DEFAULT NULL	--> Uses NULL as the default value.

Teradata:

UPPERCASE	--> Stores the data entered in upper case.
CASESPECIFIC	--> Treats data as case specific for comparisons and sorting. By default in teradata 'A' and 'a' would mean the same and hence they will sort in any sequence. However when we make it case specific the sorting differs.
FORMAT	--> Used to control display format of a field.
TITLE	--> Used to provide default titles.
NAMED/ AS	--> Uses Default column name.
COMPRESS	--> Used to compress NULL's to take no physical space.
COMPRESS NULL	--> Used to compress NULL's to take no physical space.
COMPRESS value	--> Used to compress a particular value and NULL's to take no physical space
WITH DEFAULT	--> Uses System default values.
DEFAULT DATE	--> Uses today's date as default date.
DEFAULT TIME	--> Uses Current Time as default time.

Note that DEFAULT DATE ,DEFAULT TIME and WITH DEFAULT are all not ANSI.

Example:

CREATE SET TABLE EDW_RESTORE_TABLES.TEST3 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     DATABLOCKSIZE = 21504 BYTES, FREESPACE = 30 PERCENT, CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COLUMN1 CHAR(1) UPPERCASE)

       PRIMARY INDEX ( COLUMN1 );

insert into EDW_RESTORE_TABLES.TEST3 values ('c')

select * from EDW_RESTORE_TABLES.TEST3

Gives 'C'   Capital C