Macro vs Procedure

Database

Macro vs Procedure

Hi,
The diff. between Macros and stored procedure in Teradata is that
stored procedure require physical storage.

Can anyone clarify what does physical storage mean
and how a stored procedure consume physical storage?

I assume the stored procedure is compiled and stored in the data dictionary.
Please correct if I am wrong.
Tags (2)
2 REPLIES
N/A

Re: Macro vs Procedure

There are more differences than just that. But you are correct. Stored procedures are compiled and stored in the database. They're typically small, but the more complex they are, the bigger they get. This query will show you how much space they take up:

SELECT
TB.DatabaseName
,TB.TableName
,SUM(TS.CurrentPerm)
FROM
"DBC"."Tables" AS TB
INNER JOIN
"DBC".TableSize AS TS
ON TB.TableName = TS.TableName
AND TB.DatabaseName = TS.DataBaseName
WHERE
TableKind = 'P'
GROUP BY
1
,2
ORDER BY
3 DESC;

A macro, however, requires no space. Simply change out the TableKind = 'P' for TableKind = 'M' in the above query to see what I mean. It's basically like a view in that regard.

Re: Macro vs Procedure

I fired the query in database.
For SPs, I got the permspace while for macros no rows were returned.

Thanks a lot Mnylin