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