Tera Data Interview Questions and Answers
Question - 101 : - Explain Surrogate Keys in Teradata?
Answer - 101 : -
Situations sometimes occur where the identification and choice of a simple primary key are difficult, if not impossible. There might be no single column that uniquely identifies the rows of a table or there might be performance considerations that argue against using a composite key. In these situations, surrogate keys are an ideal solution.
A surrogate key is an artificial simple key used to identify individual rows uniquely when there is no natural key or when the situation demands a simple key, but no natural non-composite key exists. Surrogate keys do not identify individual rows in a meaningful way: they are simply an arbitrary method to distinguish between them.
Question - 102 : - Explain the BLOCK COMPRESSION?
Answer - 102 : -
Use this option to set the temperature-based block compression state of a table.
Teradata Virtual Storage tracks data temperatures at the level of cylinders, not tables, and the file system obtains its temperature information from Teradata Virtual Storage, so it also handles temperature-related compression at cylinder level.
Question - 103 : - Explain the DATABLOCKSIZE in Teradata?
Answer - 103 : -
- DATABLOCKSIZE sets the maximum data block size for blocks that contain multiple rows. The data block is the physical I/O unit for the Teradata file system.
- Larger block sizes enhance full table scan operations by selecting more rows in a single I/O. Smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed.
Question - 104 : - Explain the MERGEBLOCKRATIO in Teradata?
Answer - 104 : -
The MERGEBLOCKRATIO option provides a way to combine existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. This option is not available for volatile and global temporary files. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that would otherwise consist mainly of small data blocks.
Question - 105 : - Explain Permanent Journaling?
Answer - 105 : -
The permanent journal is a user-specified table that can be used to capture both before images and after images of Teradata Database transactions. Journal entries in this table can be used by the Archive/Recovery utility to roll forward or rollback transactions during a recovery operation.
Question - 106 : - What is LOG and NO LOG in Teradata?
Answer - 106 : -
Global temporary and volatile tables permit you to define whether their activity is logged to the transient journal. While the NO LOG option reduces the system overhead of logging, it is also true that table modifications are lost and cannot be recovered upon an aborted SQL request.
Question - 107 : - Explain about Volatile Tables in Teradata?
Answer - 107 : -
The primary index for a volatile table can be nonpartitioned or row-partitioned. The table can also be defined without a primary index (NoPI).
The following options are not permitted for volatile tables.
- Referential integrity constraints
- CHECK constraints
- Permanent journaling
- DEFAULT clause
- TITLE clause
- Named indexes
- Column partitioning
- Primary AMP index
Question - 108 : - What is Hot Standby Nodes (HSN) in Teradata?
Answer - 108 : -
Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata process.
Question - 109 : - Explain CHECK Constraints in Teradata?
Answer - 109 : -
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.
Question - 110 : - Explain Referential Constraints in Teradata?
Answer - 110 : -
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.