Teradata Useful Information Q/A for freshers and Expert Levels

Database
Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

How to create a table with an existing structure of another table with or without data and also with stats defined in Teradata?

CREATE TABLE new_TABLE AS old_TABLE WITH DATA

CREATE TABLE new_TABLE AS old_TABLE WITH NO DATA

CREATE TABLE new_TABLE AS old_TABLE WITH DATA AND STATS

Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

How to find the duplicate rows in the table in Teradata?

Group by those fields and add a count greater than 1 condition for those columns

For example –

SELECT name, COUNT (*) FROM TABLE EMPLOYEE GROUP BY name HAVING COUNT (*)>1;

Also  DISTINCT will be useful. If both DISTINCT and COUNT(*) returns same number then there are no duplicates.

Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

Which is more efficient GROUP BY or DISTINCT to find duplicates in Teradata?

With more duplicates GROUP BY is more efficient while if we have fewer duplicates the DISTINCT is efficient.

Highlighted
Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

What is the difference between TIMESTAMP (0) and TIMESTAMP (6) in teradata?

Both has the Date and Time Values. The major difference is that TIMESTAMP (6) has microsecond too.

Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

What is spool space and when running a job if it reached the maximum spool space how you solve the problem in Teradata?

Spool space is the space which is required by the query for processing or to hold the rows in the answer set. Spool space reaches maximum when the query is not properly optimized. We must use appropriate condition in WHERE clause and JOIN on correct columns to optimize the query. Also make sure unncessary volatile tables are dropped as it occupies spool space.

Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

Why does varchar occupy 2 extra bytes?

The two bytes are for the number of bytes for the binary length of the field.

It stores the exact no of characters stored in varchar

Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

What is the difference between User and database in Teradata?

- User is a database with password but database cannot have password

- Both can contain Tables , views and macros

- Both users and databases may or may not hold privileges

- Only users can login, establish a session with Teradata database and they can submit requests

Enthusiast

Re: Teradata Useful Information Q/A for freshers and Expert Levels

What are the types of HASH functions used in teradata?

These are the types of HASH, HASHROW, HASHAMP and HASHBAKAMP. Their SQL functions are-

HASHROW (column(s))

HASHBUCKET (hashrow)

HASHAMP (hashbucket)

HASHBAKAMP (hashbucket)

To find the data distribution of a table based on PI, below query will be helpful. This query will give the number of records in each AMP for that particular table.

SELECT HASHAMP(HASHBUCKET(HASHROW(PI_COLUMN))),COUNT(*) FROM TABLENBAME GROUP BY 1.