MSDN Tutorials: SQL Server

Understanding SQL Server Views

Views are virtual tables based on the result-set of a SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add functions and WHERE clauses to a view and present the information as if the data resided in one single table.

What is a View?

A view is a stored query that can be treated as a virtual table. It doesn't store data itself but rather retrieves data from underlying tables when queried. This offers several advantages:

• Simplification: Complex queries can be hidden behind a view, making it easier for users to access the data they need without understanding the underlying complexity.
• Security: Views can be used to restrict access to certain columns or rows of a table, enhancing data security.
• Consistency: Ensures that data is presented in a consistent format across different applications.
• Data Abstraction: Allows you to present data from multiple tables as a single source, abstracting the physical structure of the database.

Creating a Simple View

To create a view, you use the CREATE VIEW statement. Here's a basic example that creates a view named CustomerOrdersSummary showing customer names and the total number of orders they've placed.

CREATE VIEW CustomerOrdersSummary AS
SELECT
    c.CustomerID,
    c.CompanyName,
    COUNT(o.OrderID) AS TotalOrders
FROM
    Customers AS c
JOIN
    Orders AS o ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID, c.CompanyName;
            

After creating this view, you can query it just like a regular table:

SELECT * FROM CustomerOrdersSummary WHERE TotalOrders > 5;
            

Modifying a View

If you need to change an existing view, you can use the ALTER VIEW statement. This is similar to CREATE VIEW but updates the definition of an existing view.

ALTER VIEW CustomerOrdersSummary AS
SELECT
    c.CustomerID,
    c.CompanyName,
    COUNT(o.OrderID) AS NumberOfOrders
FROM
    Customers AS c
LEFT JOIN
    Orders AS o ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID, c.CompanyName;
            

Notice the change from TotalOrders to NumberOfOrders and the use of a LEFT JOIN to include customers with zero orders.

Dropping a View

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

DROP VIEW CustomerOrdersSummary;
            

Indexed Views

SQL Server also supports indexed views. Unlike regular views, indexed views store their data physically, allowing for much faster query performance when the view is frequently accessed. To create an indexed view, specific requirements must be met, including using the SCHEMABINDING option during view creation and creating a unique clustered index on the view.

Benefits of Using Views

• Data Security: Limit access to sensitive data by exposing only necessary columns and rows.
• Simplified Queries: Abstract complex joins and calculations into a single, easy-to-use object.
• Logical Data Independence: The structure of the data stored in the database can change without affecting existing applications that use views.
• Consistent Data Presentation: Ensure data is presented uniformly, regardless of the application accessing it.

Best Practices

• Use descriptive names for your views.
• Be mindful of performance, especially with complex views or when updating underlying tables.
• Consider indexed views for performance-critical scenarios.
• Use SCHEMABINDING to prevent changes to underlying table structures that would break the view.

Views are a powerful tool in SQL Server for managing data access, simplifying complex operations, and enhancing security. Understanding how to create, modify, and utilize them effectively is crucial for any database developer or administrator.