41.Highlight the differences between Primary Key and Primary Index?

• Primary index is quite mandatory, whereas Primary Key is optional.
• Primary Index has a limit of 64 tables/columns, whereas Primary Key does not have any limit.
• Primary Index allows duplicates and nulls, whereas Primary Key doesn’t.
• Primary Index is a physical mechanism, whereas Primary Key is purely logical mechanism.

42.Explain how spool space is used?

Spool space in Teradata is basically used for running queries. Out of the total space that is available in Teradata, 20% of the space is basically allocated to spool space.

43.Highlight the need for Performance Tuning?

Performance tuning in Teradata is basically done to identify all the bottlenecks and then resolve them.

44.Comment whether bottleneck is an error or not?

Technically, bottleneck is not a form of error, but it certainly causes a certain amount of delay in the system.

45.How can bottlenecks be identified?

There are basically four ways of identifying a bottleneck. These are: –

• Teradata Visual Explain
• Explain Request Modifier
• Teradata Manager
• Performance Monitor

46.What is meant by a Highest Cost Plan?

As per Highest Cost Plan, the time taken to execute the process is more, and it takes the longest path available

47.Highlight all the modes that are present under Confidence Level?

Low, No, High and Join are the four modes that are present under Confidence Level.

48.Name the five phases that come under MultiLoad Utility?

Preliminary Phase, DML Phase, Data Acquisition Phase, Application Phase and End Phase.

49.Highlight the limitations of TPUMP Utility?

Following are the limitations of TPUMP utility: –

• We cannot use SELECT statement.
• Data Files cannot be concatenated.
• Aggregate and Exponential operators are not supported.
• Arithmetic functions cannot be supported.

50.In BTEQ, how are the session-mode parameters being set?

set session transaction BTET -> Teradata transaction mode
.set session transaction ANSI -> ANSI mode
These commands will work only when they are entered before logging into the session.

51.My table got locked during MLOAD due to a failed job. What do I do to perform other operations on it?

Using RELEASE MLOAD. It removes access locks from the target tables in Teradata. It must be entered from BTEQ and not from MultiLoad. To proceed, you can do RELEASE MLOAD.

52.How to find duplicates in a table?

53.How to you verify a complicated SQL?

I use explain statement to check if the query is doing what I wanted it to do.

54.Did u ever use UPPER Function?

UPPER Function is used to convert all characters in a column to the same characters in upper case.

55.What does a LOWER Function do?

LOWER function is used to convert all characters in a column to the lower case characters

56.How do you see a DDL for an existing table?

By using show table command.

57.Which is more efficient GROUP BY or DISTINCT to find duplicates?

With more duplicates GROUP BY is more efficient, if only a few duplicates exist DISTINCT is more efficient.

58.Syntax for CASE WHEN statement?

59.What’s the difference between TIMESTAMP (0) and TIMESTAMP (6)?

TIMESTAMP (0) is CHAR (19) and TIMESTAMP (6) is CHAR (26) Everything is same except that TIMESTAMP (6) has microseconds too.

60.How do you determine the number of sessions?

Teradata performance and workload

• Client platform type, performance and workload
• Channel performance for channel attached systems
• Network topology and performance for network attached systems.
• Volume of data to be processed by the application.