What is the difference between a view and a volatile table in Teradata? As far as I know, a volatile table is removed with the end of the session. Also it is only me who can see the volatile table as opposed to the view. Are there any other significant differences?
Answer
A volatile table is a temporary table that is only held until the end of session. This is created by default in your "personal schema" and consumes your spool space to maintain.
A view is an object that is permanent across sessions, generates from tables existing in the environment you are in, and does not consume spool space continuously.
Generally speaking I use volatile tables to upload from scripts so that I can process information and drop table easily at the end of a session. I use views to change the way a user sees information that resides in other tables or to restrict access to information to certain users without altering root tables.