More on Tables

When you store data in a table, it all resides in a single database. The data is backed up with the database. These regular tables are also known as base tables. There are other types of tables or objects that behave like tables.

A temporary table is one that exists in tempdb. This type of table lasts until your session ends and there are no references to it from other sessions. There are also objects called table variables. These store data in the context of a batch. The data is not written to disk. It only resides in memory.

Views behave like tables. However the database will only store the SQL needed to retrieve the data. When the view is accessed, the SQL is executed to produce the results. The only exception to this are indexed views. This is where the query results themselves are actually stored. In Oracle, these are called materialized views. In SQL Server, they are kept current and updated when the underlying tables change.