Example: CREATE … AS Requests That Produce a NoPI Table - Advanced SQL Engine - Teradata Database

Assume the following primary-indexed source table definition for the following example set.

     CREATE TABLE source_pi (
       column_1 INTEGER
       column_2 INTEGER)
     UNIQUE PRIMARY INDEX (column_1);

Also assume you have created the following NoPI table definition for the example set:

     CREATE TABLE source_nopi (
       column_1 INTEGER
       column_2 INTEGER)
     NO PRIMARY INDEX;

The following CREATE TABLE … AS requests all produce a NoPI table because in each case you have explicitly specified NO PRIMARY INDEX:

     CREATE TABLE target_nopi AS
       source_pi
     WITH DATA 
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS
       source_nopi
     WITH DATA
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS (
       SELECT column_1, column_2
       FROM source_pi) 
     WITH DATA 
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS (
       SELECT column_1, column_2
       FROM source_nopi) 
     WITH DATA 
     NO PRIMARY INDEX;

Unlike the preceding examples, the outcome of the following CREATE TABLE request depends on the setting of the DBS Control parameter PrimaryIndexDefault.

     CREATE TABLE target AS (
       SELECT column_1, column_2
       FROM source_nopi) 
     WITH DATA;