Microsoft SQL Server

A View in SQL Server is a virtual table that consists of a SELECT query result from one or more tables. Unlike a physical table, a view does not store data itself but provides an abstraction layer over existing data. Views are particularly useful for simplifying complex queries, improving security by restricting access to specific columns or rows, and ensuring consistency by encapsulating frequently used queries.

Key Concepts of Views:

1. Creating a View
   • You can create a view using the CREATE VIEW statement, which defines the query the view is based on. The view can then be queried like a regular table.

   Example:

   CREATE VIEW StoreSalesView AS
   SELECT s.sale_id, s.sale_date, p.product_name, c.customer_name, s.total_amount
   FROM sales s
   JOIN products p ON s.product_id = p.product_id
   JOIN customers c ON s.customer_id = c.customer_id;
   

2. Selecting Data from a View
   • Once created, you can retrieve data from the view just as you would from a table. Views simplify complex queries and abstract them for easier use.

   Example:

   SELECT * FROM StoreSalesView;
   

3. Updating Data through a View
   • You can update the underlying data through a view, but there are some restrictions. The view must be updatable, meaning it can’t involve complex calculations, aggregations, or joins in certain cases.

   Example:

   UPDATE StoreSalesView
   SET total_amount  =  150
   WHERE sale_id  = 1;
   

4. Dropping a View
   • If you no longer need a view, it can be removed using the DROP VIEW statement. This doesn’t affect the underlying data, only the view definition.

   Example:

   DROP VIEW StoreSalesView;
   

5. Benefits of Using Views
   • Security: Restrict access to specific columns or rows without giving direct access to the table.
   • Simplicity: Simplify complex queries for repeated use.
   • Consistency: Ensures that commonly used queries are standardized and consistent.
   • Modularity: Abstracts complex logic into manageable components.

Additional Points on View Usage:

1. Views can be used to join multiple tables without requiring the user to write complex SQL every time.
2. Indexes on views (indexed views) can be created to enhance performance, but this is an advanced topic not covered for beginners.
3. Views are often used in reporting and dashboards, where only specific columns and rows need to be displayed to users.

This introduction to views provides a solid foundation for beginners, focusing on how to create and manage views in Microsoft SQL Server.

Definition

A view is a result set of a stored query on the data, which the database users can query just like they would in a real table. Unlike a real table, a view does not store data physically; it is a set of queries that dynamically retrieve data from the database tables as needed.

Uses and Advantages

• Security: Views can be used to restrict user access to specific rows and columns of data.
• Simplicity: Complex queries can be encapsulated in views.
• Consistency: Views can present a consistent, unchanging image of the structure of the database.
• Logical Data Independence: Views provide a layer of abstraction.
• Data Integrity: Views can be used to ensure data integrity by exposing only valid data to the user.

Types of Views

• Updatable Views: Views that allow data modification operations.
• Read-Only Views: Views that do not allow data modification operations.
• Materialized Views: A materialized view is a database object that contains the results of a query and is stored on disk, acting like a cache to improve query performance. Unlike standard views that dynamically retrieve data every time they are accessed, materialized views hold the actual query result and can be indexed, thus significantly speeding up access to complex aggregated or joined data. They are particularly useful in data warehousing scenarios where queries are run on large volumes of data and require heavy computation. However, because they store a snapshot of the data, materialized views must be refreshed periodically to remain up-to-date with the underlying data changes. This trade-off between data freshness and query speed is a key consideration when deciding to use materialized views in a database system.

Creating a View

Here is the basic SQL syntax for creating a view:

Example

Consider you have a table employees with columns id, name, salary, department. You can create a view to show only the name and department:

Modifying a View

To change a view, you generally have to drop and recreate it. Some SQL dialects support the CREATE OR REPLACE VIEW statement.

Removing a View

To remove a view from the database, you use the DROP VIEW statement:

Limitations and Considerations

• Views do not have associated indexes, so queries might be slower than direct table queries.
• Some views are not updatable.
• Overusing views can lead to maintenance challenges and performance issues.

In summary, views are a powerful feature in SQL databases that provide a way to simplify complex operations, enhance security, and offer a level of abstraction for database operations.

DATABASE VIEW EXAMPLE

Let's establish a database view to consolidate data for the order management system. Our objective is to extract the client's name, their order numbers, including the order date, order number, number of products ordered, order status, order amount, paid amount, and the agent who processed the order on their behalf. This requirement is depicted in the data model below, with the necessary columns highlighted with red lines.

CREATE VIEW get_order_details AS
SELECT 
    org_client.first_name AS client_name,
    act_order.order_number,
    act_order.order_date,
    act_lkp_order_status.order_status,
    COUNT(act_order_detail.product_id) AS product_count,
    SUM(act_order.sub_total + act_order.tax_amount + act_order.shipping_amount) AS invoice_amount,
    SUM(act_payment.paid_amount) AS paid_amount,
    org_employee.first_name AS booking_agent_name
FROM act_order
INNER JOIN act_lkp_order_status ON act_lkp_order_status.order_status_id = act_order.order_status_id
INNER JOIN act_order_detail ON act_order_detail.order_id = act_order.order_id
LEFT JOIN act_payment ON act_payment.order_id = act_order.order_id
INNER JOIN org_client ON org_client.client_id = act_order.client_id
INNER JOIN org_employee ON org_employee.employee_id = act_order.sales_agent_employee_id
GROUP BY 
    org_client.first_name,
    act_order.order_number,
    act_order.order_date,
    act_lkp_order_status.order_status,
    org_employee.first_name
ORDER BY act_order.order_date;