Both may own objects such as tables, views, macros, procedures, and functions. Both users and databases may hold privileges.
However, only users may log on, establish a session with the Teradata Database, and submit requests.
A user performs actions where as a database is passive. Users have passwords and startup strings; databases do not. Users can log on to the Teradata Database, establish sessions, and submit SQL statements; databases cannot.
Creator privileges are associated only with a user because only a user can log on and submit a CREATE statement. Implicit privileges are associated with either a database or a user because each can hold an object and an object is owned by the named space in which it resides
=========================================================================
When a PI is not mentioned on a table, how will Teradata consider the PI for that table?
If you don't specify a PI at table create time then Teradata must chose one. For instance, if the DDL is ported from another database that uses a Primary Key instead of a Primary Index, the CREATE TABLE contains a PRIMARY KEY (PK) constraint. Teradata is smart enough to know that Primary Keys must be unique and cannot be null. So, the first level of default is to use the PRIMARY KEY column(s) as a UPI.
If the DDL defines no PRIMARY KEY, Teradata looks for a column defined as UNIQUE. As a second level default, Teradata uses the first column defined with a UNIQUE constraint as a UPI.
If none of the above attributes are found, Teradata uses the first column defined in the table as a NON-UNIQUE PRIMARY INDEX (NUPI).
=========================================================================
What is covered query in Teradata?
If a SELECT query covers all the columns that are defined in the JOIN INDEX as join columns, such type of queries are called as COVERED query.
Multi-Column NUSI Columns used as a Covered Query
=========================================================================
What is the use of WITH CHECK OPTION in Teradata?
In Teradata, the additional key phase: WITH CHECK OPTION, indicates that the WHERE clause conditions should be applied during the execution of an UPDATE or DELETE against the view. This is not a concern if views are not used for maintenance activity due to restricted privileges.
=========================================================================
What is identity column?
Teradata has a concept of identity columns on their tables beginning around V2R6.x. These columns differ from Oracle's sequence concept in that the number assigned is not guaranteed to be sequential. The identity column in Teradata is simply used to guaranteed row-uniqueness.
Example:
CREATE MULTISET TABLE MyTable
(
ColA INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 20)
ColB VARCHAR(20) NOT NULL
)
UNIQUE PRIMARY INDEX pidx (ColA);
Granted, ColA may not be the best primary index for data access or joins with other tables in the data model. It just shows that you could use it as the PI on the table.
=========================================================================
How to implement UPSERT logic in Teradata using SQL?
We have MERGE-INTO option available in Teradata data which works as an UPSERT logic in teradata. If the condition matches then update else insert.
Example: MERGE into dept_table1 as Taregt
USING (SELECT dept_no, dept_name, budget
FROM dept_table where dept_no = 20) Source
ON (Target.dept_no = 20)
WHEN MATCHED then
UPDATE set dept_name = ëBeing Renamedí
WHEN NOT MATCHED then
INSERT (dept_no, dept_name, budget)
VALUES (source.dept_no, source.dept_name, source.budget);
=========================================================================
What is value ordered NUSI?
When we define a value ordered NUSI on a column the rows in the secondary subtable get sorted based on the secondary index value. The columns should be of integer or date type.
This is used for range queries and to avoid full table scans on large tables.
=========================================================================
What are the DBQL tables.
Database Query Log tables are the tables present in DBC database which store the history of all the operations performed on the tables present in the databases.
The history could get very large so these tables should be purged when the data is no longer needed.
=========================================================================
What is the difference between sample and top?
The Sampling function (SAMPLE) permits a SELECT to randomly return rows from a Teradata database table.
It allows the request to specify either an absolute number of rows or a percentage of rows to return. Additionally, it provides an ability to return rows from multiple samples.
SELECT * FROM student_course_table SAMPLE 5;
The TOP clause is used to specify the number of records to return. The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
SELECT TOP 2 * FROM EMP.
=========================================================================
What is difference between ZEROIFNULL and NULLIFZERO ?
The ZEROIFNULL function: will pass zero when data coming as null
The NULLIFZERO function: will pass null when data coming as zero.
=========================================================================
What is Range_N?
Range_N is defined on a partition primary index to specify the range of values of a column that should be assigned to a partition.
The number of partitions = the number of ranges specified + no case + unknown
no case ñ> if the value does not belong to any range
unknown -> for the values like nulls, spaces etc
=========================================================================
What is Casting in Teradata ?
It will convert the data type
The casting is similar to DDL:
CAST('02/03/2009-01:25:11' AS TIMESTAMP FORMAT 'MM/DD/YYYY-HH:MI:SS')
=========================================================================
Can we do collect stats on a table when the table is being updated?
No.We never collect stats on a table when the table is being updated. It will go for dead lock.
=========================================================================
When will you go for hash index?
A hash index organizes the search keys with their associated pointers into a hash file structure.
We apply a hash function on a search key to identify a bucket, and store the key and its associated pointers in the bucket (or in overflow buckets).
Strictly speaking, hash indices are only secondary index structures, since if a file itself is organized using hashing, there is no need for a separate hash index structure on it.
=========================================================================
Difference between Global and Volatile Temporary Table? Where they can be used and how they can be used?
Global Temporary Tables
- Materialize in users temporary space
- Base definitions are permanent in Data Dictionary tables
- Definitions can be materialized by any user with the necessary DML privileges
- Can be defined for COLLECT STATISTICS
- Can survive a Teradata Database restart
- Up to 2000 materialized tables per session
- Materialized instance is local to a session
- If not dropped manually, instance is dropped automatically at session end
- An instance an be dropped manually any time during a session with DROP TEMPORARY TABLE
- Materialized contents are not shareable with other sessions
- A very large number of global tables can prolong logoff, because materialized global tables are dropped automatically at session end.
Volatile Tables
- Occupy space in users SPOOL allocation
- Definitions are kept in cache and go away at session end or during a Teradata Database restart
- Private to the session in which they are created
- Can be defined for COLLECT STATISTICS(TD14 feature)
- Do not survive a Teradata Database reset
- Up to 1000 tables per session
- Local to a session
- If not dropped manually, dropped automatically at session end
- Can be dropped manually any time during a session with DROP TABLE
- Contents are not sharable with other sessions
- A very large number of volatile tables can prolong logoff, because volatile tables are dropped automatically at session end.
Interview Questions Part-1 Interview Questions Part-3
