Summary of "Advanced SQL| SQL Views Explained & Examples"

Main ideas / concepts explained

What a SQL view is (purpose)

A view is presented as a layer between applications/users and the underlying database tables. Instead of users/applications reading raw tables directly (which could expose too much data), they read only the subset/shape of data exposed by the view.

Why views are useful

Security / data protection
- Prevents an application from accessing the entire underlying table.
- Users see only what the view returns (not the full underlying tables).
Simplifying complex queries
- If retrieving data requires complex joins/calculations across multiple tables (e.g., “all of a user’s transactions today”), a view can encapsulate that logic.
- Consumers can query the view with simpler SQL like:
  - SELECT ... FROM view
Consistent reusable “calculation rules”
- If many parts of the system need the same calculations, views centralize the logic so everyone uses the same definition of results.

How views behave

A standard view is not stored as a physical copy.
When a request is made to the view, the underlying query runs at request time, and the view’s result is produced then.

Therefore:

Results reflect the latest changes in the underlying tables (because it re-executes).
Performance can be worse than precomputed alternatives because the query work happens on each request.

Updatability / modifying views

The notes emphasize that updating/deleting via views may be limited, especially for multi-table views:

Since a view is defined by a query (often involving joins/parts of multiple tables), it may not support direct update/delete operations by default.
Changes in the base tables affect the view results automatically because the view is computed from them.

View vs Index View (materialized/physical snapshot concept)

An Index View (also described as similar to “materialized/snapshots”) is stored physically in the database.
When created, it stores a snapshot of the view results so it can be returned faster.

Tradeoffs mentioned:

Index/materialized view may not be instantly up to date (refresh timing depends on the scenario).
Creation/maintenance and execution characteristics differ.
Standard views compute results at query time; index views shift computation to an earlier time.

Methodology / step-by-step workflow (as described)

A) Creating and using a standard view (SQL Server style workflow)

Identify the base tables the view should be based on.
Use a CREATE VIEW command:
- Provide a view name (often prefixed with something like VW_... in the explanation).
- Define the view by writing the SELECT query that returns the desired columns and joins.
After creating it:
- Refresh the Object Explorer to see the view appear under the appropriate database/schema (example mentioned: “Sales” and a “Product Data” view).
Query the view like a table:
- Example pattern: SELECT ... FROM <view>
- The view’s underlying query executes, so returned data reflects base table changes.

B) Updating/dropping views (high-level)

The video states it covers:

Changing the view name
Deleting / dropping the view
Updating the view

(Exact SQL syntax isn’t clearly captured, but these are typical administrative operations.)

C) Example logic described for a “Daily Sales / Delivery Sales” view

The view is described as combining information such as:

Year
Order ID
Product/item details
List price (explicitly mentioned as coming from something like P.ListPrice)

The relationships described connect orders to products/items.

After building the view:

The consuming user does not need to understand the underlying joins/calculations.
Consumers simply query the view (e.g., “Daily Sales”) to get correctly assembled results.

D) Inspecting view metadata / dependencies

The subtitles describe multiple ways to find what a view is and how it’s defined:

Method 1: Determine object type and schema
- Query system metadata to identify that the object is a view (described via an object type value such as V).
- Use functions to derive the schema name from object id.
Method 2: Retrieve the underlying SQL definition
- Use system modules/metadata (described as “SQL Module”) to show the SELECT statement the view is based on.
- Look up by object ID associated with the view.
Method 3: Provide the view ID by name
- If you only know the view name, use metadata/system lookup to get its object ID, then use that ID to fetch definition details.

Sources / speakers featured

Primary speaker: Unidentified narrator/instructor (spoken throughout; no name provided).
Sources: None beyond references to SQL Server/DBMS catalog metadata conceptually (e.g., Object Explorer and system functions/modules).