Macro in Teradata

What is macro. What is the difference between macro and procedure.

A macro is the set of sql statements which can be run as a single statement.
          Macro                                                                    Procedure
Macro returns set of data/rows to the user.     It does not return data/rows to the user.
It is allows only input values.                            It provides Input/Output parameter.
It is stored in DBC Perm space.                    It is stored in DATABASE/USER Perm space.

To create a macro:

CREATE MACRO macro_name AS
(SELECT eid, ename  FROM emp WHERE deptno=20;);
 To execute a macro:
 EXEC macro_name; 

To create a parametrized macro:
CREATE MACRO macro_name( in_param DATATYPE)
(SELECT eid, ename  FROM emp WHERE deptno= :in_param;);
To execute a macro:
EXEC <macro_name> [parameter value list];

Performance Tuning in Teradata

This post to be released soon.

Introduction of Teradata Database

The concept of teradata system is little bit different. There is only one database(your teradata system) with lots of (schemas/users). Here (schemas/users) is called database.
In this system dbc is the owner of all metadata. In dbc you can find all the object of teradata.

To get all tables name which consist the particular column.

select tablename, columnname from dbc.columns where columnname ='Emp_Id' and databasename = 'EMPDB';

To see all the objects in a particular database.

Help database databasename;

Index in Teradata

What is index. How many types of indexes in Teradata.
=> Index is physical mechanism which is used to access and distribute the data rows.
      Indexes help to fast retrieval of data set and useful to data distribution across AMP.

=>There are four main types of indexes in Teradata.
    * Primary Index
    * Secondary Index
    * Join Index
    * Hash Index

=> These indexes divided into following sub category.
    * Unique Primary Index (UPI)
    * Non-Unique Primary Index (NUPI)
    * Partitioned Primary Index(PPI)
    * Non-Partitioned Primary Index(NPPI)
    * Unique Secondary Index (USI)
    * Non-Unique Secondary Index (NUPI)

=> Join Index has six subtypes viz:
    * Single table join index(STJI)
    * Single table aggregate join index(STAJI)
    * Single table sparse join index
    * Multitable simple join index
    * Multitable aggregate join index
    * Multitable sparse join index