Saturday, 25 May 2013

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. 


2 comments:

  1. This info you provided in the blog that was really unique I love it!!! ppi claims

    ReplyDelete
    Replies
    1. Thanks for your feedback Henry. I am recently working on Tableau integration with the DW. Would keep your guys posted !!

      Delete

Please share your thoughts and let us know the topics you want covered