Secondary Indexes and PPI in a Create table - Create column as Varchar(0) - Scenario for Teradata SQL exam

There is a basic difference between the below 2 statements:

1. CREATE TABLE Test1.TableA as Test1.TableB with data;

The above statement creates TableA as a replica of TableB, preserving its Secondary Indexes and Partitioned Primary Indexes.

2. CREATE TABLE Test1.TableA as
     (Select * from Test.TableB)
     with data PRIMARY INDEX(Cus_ID);

The above statement creates TableA with only the data present in TableB. The Secondary Indexes and Partitioned Primary Indexes are not preserved. The table chooses the PI defined in the Create Table statement.
Also, if there is a column, say Col2 in TableB that has no data, it may get created as Varchar(0) in TableA.

So, when you do a
Show Table Test1.TableA;

you get

Cus_ID Integer Not Null,
Col2 Varchar(0) Character Set Latin

Watch out for these differences when you are tuning your queries!!


Like us if you are helped.