Views

A virtual table.

Introduction

A View in MS SQL is a virtual table created from a query result. It is essentially a stored SELECT statement that allows users to simplify complex queries and present data in a user-friendly format without storing the data physically. Views help streamline data management, improve security, and make data more accessible.

Why Use Views?

  • Simplifying Complex Queries: Views allow you to encapsulate complex JOINs, GROUP BY, and filtering logic, making it easier to access and use the data.
  • Security: You can restrict access to specific columns or rows of a table by granting users access to a view instead of the underlying tables.
  • Consistency: Views ensure that users see a consistent set of data, as they always run the same pre-defined query.
  • Data Abstraction: They hide the complexity of the underlying schema, making it easier for users to work with the data.

Creating a View

The CREATE VIEW statement is used to define a new view based on a SELECT query. Once created, the view can be queried just like a regular table.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Creating a view to display basic customer information.

CREATE VIEW CustomerView AS
SELECT customer_id, first_name, city, email
FROM Customers
WHERE IsActive = 1;

This view shows only active customers and provides a simplified way to retrieve customer information.

Using a View

Once a view is created, you can query it like a regular table.

Example:

SELECT * FROM CustomerView;

This query will return the data from the CustomerView as if it were querying a regular table.

Updating Data Through Views

In many cases, you can update the underlying table through a view, as long as the view adheres to certain conditions (e.g., it doesn’t include aggregate functions or joins). MS SQL allows updates on views, but there are some restrictions.

Example: Updating customer email through the view.

UPDATE CustomerView
SET Email = 'newemail@example.com'
WHERE customer_id = 101;

This will update the email of the customer with customer_id = 101 in the Customers table.

However, not all views are updatable, especially if they involve:

  • Joins across multiple tables.
  • Aggregate functions like SUM(), AVG().
  • GROUP BY clauses.

Modifying Views

If you need to change the query behind a view, you can use the ALTER VIEW statement to redefine it.

Example: Modifying the CustomerView to include only customers from New York.

ALTER VIEW CustomerView AS
SELECT customer_id, first_name, City, Email
FROM Customers
WHERE IsActive = 1 AND City = 'New York';

Deleting a View

If a view is no longer needed, you can remove it using the DROP VIEW statement. Deleting a view does not affect the underlying tables.

Syntax:

DROP VIEW view_name;

Example: Dropping the CustomerView.

DROP VIEW CustomerView;

Types of Views

  1. Simple Views:

These views are based on a single table and do not include complex logic such as joins or aggregates. Simple views are often updatable.

Example:

CREATE VIEW ActiveCustomers AS
SELECT customer_id, first_name, Email
FROM Customers
WHERE IsActive = 1;
  1. Complex Views:

These views involve multiple tables, joins, and potentially aggregate functions. Complex views are usually read-only due to their complexity.

Example:

CREATE VIEW CustomerOrderSummary AS
SELECT c.first_name, COUNT(o.order_id) AS OrderCount, SUM(o.total_amount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.first_name;

This complex view provides a summary of the number of orders and the total amount spent by each customer.

Indexed Views

An Indexed View (also called a materialized view) stores the results of the view physically in the database, making it faster to retrieve data. Indexed views are useful for improving performance on complex queries, especially when involving large data sets and heavy computations.

Creating an Indexed View:

To create an indexed view, you first create a standard view, then add an index to it.

Example:

CREATE VIEW CustomerOrderSummary WITH SCHEMABINDING AS
SELECT c.customer_id, COUNT_BIG(o.order_id) AS OrderCount
FROM dbo.Customers c
JOIN dbo.Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- Create index on the view
CREATE UNIQUE CLUSTERED INDEX IX_CustomerOrderSummary
ON CustomerOrderSummary (customer_id);

The WITH SCHEMABINDING clause is required for indexed views to ensure the underlying tables cannot be altered in a way that would invalidate the view.

Best Practices

  • Use Views for Security: Create views that expose only the necessary columns to certain users, protecting sensitive data.
  • Optimize Complex Queries: Use views to encapsulate complex joins and filters, reducing the need for repetitive query writing.
  • Avoid Overcomplicating Views: Keep views as simple as possible. If they become too complex, consider breaking them into multiple views.
  • Consider Indexed Views for Performance: If performance is critical and the data is queried frequently, consider using indexed views to speed up query execution.

Conclusion

Views are a powerful tool in MS SQL, enabling data abstraction, improved security, and more manageable query writing. They offer a way to simplify complex operations, hide the details of database schema, and optimize performance when used correctly.

By understanding how and when to use views, you can ensure more efficient and secure data access, especially in large, complex databases like those used in customer management systems.

Asked in Interview

  • What is a View? How is it different from a Table?
  • Can we create a View based on another View?
  • Can a View be updated or modified?
  • What are the different types of Views in SQL?
  • Do Views improve query performance?
  • How do Views enhance security in a database?
  • What are the limitations of using Views in SQL?
  • Write a query to create a view that shows only the names and email addresses of active customers from a Customers table.
  • How would you alter an existing view to add a new column?