Teradata Interview Questions Set 04

61. Explain CHECK Constraints in Teradata?

CHECK constraints are the most general type of SQL constraint specification. Depending on its position in the CREATE TABLE or ALTER TABLE SQL text, a CHECK constraint can apply either to an individual column or to an entire table.

62 Explain Referential Constraints in Teradata?

In some circumstances, the Optimizer is able to create significantly better query plans if certain referential relationships have been defined between tables specified in the request. The Referential Constraint feature also referred to as soft referential integrity, permits you to take advantage of these optimizations without incurring the overhead of enforcing the suggested referential constraints.

63. What is the ADD Option in Teradata?

The ADD option reserves additional partition numbers for a partitioning level to enable adding partitions to a partitioning level at a later time using an ALTER TABLE statement The following rules apply to the ADD clause for a row or column partition.

64. Difference between PRIMARY KEY and UNIQUE Constraints Versus Primary Indexes?

You can define the primary index for a table using either a PRIMARY KEY or UNIQUE constraint as the default primary index in a CREATE TABLE statement.

The following bullets list the rules for defining primary keys and UNIQUE constraints with respect to primary indexes:

• A table can have at most 1 primary key and need not have a primary index.
• If a table has a primary index, it can have only 1.
• You cannot define a primary index and a PRIMARY KEY or UNIQUE constraint on the same column set.

You can still define a relationship for referential integrity by referencing the UPI of a table even if no primary key is defined explicitly for that table because it is always valid to define a referential integrity relationship with an alternate key.

• If both a primary index and primary key are specified in a CREATE TABLE statement, then the primary index is the hashing index and the primary key is mapped to a unique secondary index by default.
• If a primary key is specified in a CREATE TABLE statement, but a primary index is not, then the system maps the primary key to a UPI by default.
• If neither primary index nor primary key is specified in a CREATE TABLE statement, then the system defines the first column that has a UNIQUE constraint as the UPI by default.
• If there is no PRIMARY INDEX, PRIMARY KEY, or UNIQUE constraint defined in a CREATE TABLE statement, and the PrimaryIndexDefault parameter is set to either D or P, then Teradata Database defines the first index-eligible column defined for the table to be its primary index.

The system defines this index as a NUPI by default except for the case of a single column table defined with the SET (no duplicate rows permitted) option, in which case the system defines it as a UPI.

1. If there is no PRIMARY INDEX, PRIMARY KEY constraint, or UNIQUE constraint defined in a CREATE TABLE statement, and the PrimaryIndexDefault parameter is set to N, then Teradata Database creates the table with no primary index.
2. Columns defined with either of the following constraints cannot be defined to be nullable.                                                                    1. PRIMARY KEY   2. UNIQUE
3. Columns defined with any of the following constraints can be defined as nullable:                                                                                  1. PRIMARY INDEX    2. UNIQUE PRIMARY INDEX   3. INDEX   4. UNIQUE INDEX

You should declare the column set that constitutes these index types to be NOT NULL unless there is a compelling reason not to.

• You cannot define a PRIMARY KEY or UNIQUE constraint with the same column set as a secondary index defined on the same table.
• You cannot define a UNIQUE constraint explicitly on the same columns as a PRIMARY KEY constraint.

65. Explain QUEUE Keyword?

Each queue table you define must stipulate the keyword QUEUE as 1 of the CREATE TABLE options following the table name; otherwise, the table you define does not have the properties associated with queue tables and you cannot use consume mode when you select from it (see SQL Data Manipulation Language).

66. What is the QITS Column in Teradata?

The first column defined for any queue table must be a Queue Insertion Time Stamp (QITS) column. Each queue table has only 1 QITS column, and it must be defined exactly as indicated with the following attributes.

QITS_column_name TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

where QITS_column_name indicates the name you specify for the QITS column.

67. How REPLACE TRANSFORM Differs From CREATE TRANSFORM in Teradata?

For REPLACE TRANSFORM:

• If the specified transform group exists, the system replaces it with the new definition.
• If the specified transform group does not exist and the associated UDT does not have a defined transform group, the system creates the specified transform group.

68. What are Transition Tables in Teradata?

A transition table is a dynamically created table that you can reference using a correlation name.

69. Why is DATABASE Statement Not Valid In 2PC Mode?

In the 2PC mode, DATABASE is treated as a DDL statement, so it is not valid.

70. What are the different types of Teradata utilities?

There are various utilities available in Teradata. They are: 

• Batch Teradata Query (BTEQ)
• FAST EXPORT
• FAST LOAD
• MULTI LOAD
• Teradata Parallel Data Pump(TPump)
• Teradata Parallel Transport (TPT)

71. What is FAST LOAD in Teradata?

In Teradata, the FAST LOAD option loads a huge amount of data from a flat file into EMPTY tables.

72. Can you share a few advantages of Teradata?

There are many advantages of using the Teradata database, they are:

• Larger Warehouses: Teradata supports huge warehouse data than all its competitors. 
• It’s Scalable: In the Teradata database, we can store from 100 GB to over 100+ Petabytes of data on a single system. We can scale this huge data without affecting any performance-related issues.
• Parallel Aware Optimizer: Parallel aware optimizer makes query tuning simplifies query running.
• Automatic Data Distribution: Automatic Data Distribution is a great feature in Teradata, it enhances the performance of applications. It eliminates complex indexing schemes with even data distribution to reduce the time-consuming and reorganizations.
• It supports Concurrent Users: Teradata can handle the various complex queries often run by users simultaneously.
• Parallel Architecture: Teradata runs on Parallel Architecture to support unconditional parallelism.
• Supports Ad-Hoc Queries: Teradata built with ad-hoc SQL queries to support a particular purpose. This helps developers in increasing productivity.
• Teradata Manager: Teradata Manager is a central place to control the Database.
• Teradata Lowest Total Cost: Teradata is the only vendor, which provides the lowest total cost of ownership.
• Fault Tolerance: In Teradata, fault tolerance is the built-in feature, due to this feature data availability is very high and the failure ratio is very very less.

73. In how many ways can you use the Teradata database?

We can use the Teradata database in various ways, they are

1. Enterprise data warehousing
2. Active data warehousing
3. CRM
4. E­Business
5. Data marts

74. What are the main components of Teradata Architecture?

The main components of Teradata Architecture are:

• Parsing Engine(PE)
• Access Module Processors(AMPs)
• BYNETs
• Disks

75. What is BYNET and What is its purpose?

BYNET is the main component of Teradata architecture and it acts as a message-passing layer. BYNET decides which AMP should receive a message.

76. Explain about the Unique Primary Index(UPI) in Teradata?

A Unique Primary Index (UPI) is unique and cannot have any duplicates. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. A UPI enforces UNIQUENESS for a column.

78. How do you create a Unique Primary Index in Teradata?

The SQL syntax to create a Unique Primary Index is:

CREATE TABLE sample_1

 (col_a INT

 ,col_b INT

 ,col_c INT)

UNIQUE PRIMARY INDEX (col_b);

79. Explain the Non-Unique Primary Index (NUPI) in Teradata?

A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist.

80. How do you create a Non-Unique Primary Index in Teradata?

The SQL syntax to create a Non-Unique Primary Index is:

CREATE TABLE sample_2

 (col_x INT

 ,col_y INT

 ,col_z INT)

PRIMARY INDEX (col_x);