2

I suppose the definition might be different for different databases (I've tagged a few databases in the question), but suppose I have the following (in pseudocode):

CREATE VIEW myview FROM SELECT * FROM mytable GROUP BY name

And then I can query the view like so:

SELECT * FROM myview WHERE name like 'bob%'

What exactly is the "view" doing in this case? Is it just a short-hand and the same as doing:

SELECT * FROM ( SELECT * FROM mytable GROUP BY name ) myview WHERE name like 'bob%'

Or does creating a view reserve storage (or memory, indexes, whatever else)? In other words, what are the internals of what happens when a view is created and accessed?

Improve this question

2 Answers 2

Reset to default
3

A view is a name that refers to a stored SQL query. When referenced, the definition of the query are replaced in the referencing query. It is basically the short-hand that you describe.

A view is defined by the standard and is pretty much the same thing across all databases.

A view does not permanently store data. Each time it is referenced the code is run. One caveat is that -- in some databases -- the view may be pre-compiled, so the pre-compiled code is actually included in the query plan.

By contrast, some databases support materialized views. These are very different beasts and they do store data.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks for this answer. Isn't there also a "materialized view" where the data is saved, or how many different types of "views" are there?
@David542 . . . I would say that a materialized view is different from a view entirely. Both are defined by queries. But materialized views are specific to a database, may or may not be kept up-to-date (they might run on a schedule), and often limit the functionality allowed it the query. A view is really just a canned query.
would a view store metadata for the query such as the query execution plan so it doesn't have to re-run that every time, or is it literally just taking the name of the view and replacing that with the canned query? i.e., "aliasing" a query
@David542 . . . That is an implementation detail that depends on the database.
1

Some other reasons for views:

  1. Not everyone is a SQL expert so the Data Base Administrator might develop views consisting of complex joins on multiple tables to provide users easy access to the data they might need to access but might not know how to best do that.

  2. On some databases you can also create read-only views. Again, a DBA might create these to limit what operations a user can perform on certain tables.

  3. A DBA might also create a view to limit what columns of a table a user can see.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.