I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and records from various tables in a database and present all this data together in one big table. But I don't understand what kind of object View is meant to be, if it isn't a table with data types? And what sort of DML and DDL commands can be executed on View? For example, can commands like DELETE FROM, UPDATE, etc be run on it?
1 Answer 1
You can think of a view as a non-materialized query definition with a referenceable name. It doesn't store any data itself, rather when you query a view the data comes from the underlying entities that compose the query for which that view represents, effectively the tables that actually store the data at the lowest level themselves. The columns of a view take on the data types of the source entity they ultimately come from.
DML statements can be executed against a view, such as deleting and updating data, which will cause the view to act as a passthrough of such statements to the underlying tables. But generally there are restrictions on when and how a view can be updated, for example usually only columns from the same underlying table can be updated in the same statement against a view. While I couldn't find a direct reference to these restrictions in MS Access, you should be able to find somewhat helpful information on the same restrictions in updatable views in SQL Server which parallels MS Access to a degree.