All Forums Database
Kishore_1 208 posts Joined 03/10
16 Jun 2011
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.

mnylin 118 posts Joined 12/09
16 Jun 2011

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.

Kishore_1 208 posts Joined 03/10
22 Jun 2011

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

Thanks a lot Mnylin

You must sign in to leave a comment.