Contents
Views
Views in SQL are virtual tables created based on the result set of an SQL query. They allow you to encapsulate complex queries and present data in a simplified manner. Views do not store data themselves but provide a way to query the underlying tables.
1. Creating Views
Definition: Views are virtual tables that present the result set of an SQL query. They can be used to simplify complex queries, encapsulate business logic, and provide a layer of abstraction over the actual tables.
Example:
CREATE VIEW CustomerView AS
SELECT Name, Address FROM Customers;
This statement creates a view named CustomerView that shows the Name and Address columns from the Customers table. When you query CustomerView, the database retrieves data from the underlying Customers table.
Usage Example:
SELECT * FROM CustomerView;
This query retrieves all rows from the CustomerView, showing only the Name and Address columns of customers.
1.1 Updating Views
Definition: Updating views allows you to modify the data in the underlying tables through the view. This can be useful for maintaining data consistency and integrity while providing a simplified interface for data manipulation.
Example:
UPDATE CustomerView
SET Address = '123 New Street'
WHERE Name = 'John Doe';
This statement updates the Address of ‘John Doe’ in the underlying Customers table through the CustomerView. The change is reflected in the base table, ensuring data consistency.
Usage Example:
SELECT * FROM Customers WHERE Name = 'John Doe';
After the update, this query will show that ‘John Doe’ now has the address ‘123 New Street’.
1.2 Dropping Views
Definition: Dropping views removes a view from the database. This operation does not affect the underlying tables or data but only removes the virtual table.
Example:
DROP VIEW CustomerView;
This statement drops the view named CustomerView from the database. The underlying Customers table remains unaffected.
Usage Example:
Attempting to query the dropped view will result in an error:
SELECT * FROM CustomerView;
The database will return an error stating that CustomerView does not exist.
1.3 Indexed Views
Definition: Indexed views, also known as materialized views, store the result set of the view and allow for faster data retrieval. Indexed views can improve performance for frequently accessed queries by storing the precomputed result set.
Example:
CREATE VIEW SalesSummary WITH SCHEMABINDING AS
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID;
CREATE UNIQUE CLUSTERED INDEX idx_sales_summary ON SalesSummary(ProductID);
This statement creates an indexed view named SalesSummary that stores the total quantity of sales per product. The WITH SCHEMABINDING option ensures that the underlying tables cannot be altered in a way that would affect the view. The CREATE UNIQUE CLUSTERED INDEX statement creates an index on the ProductID column of the view, which stores the result set for faster retrieval.
Usage Example:
SELECT * FROM SalesSummary;
This query retrieves data from the SalesSummary view. Since it is an indexed view, the database can quickly return the precomputed results, improving performance for queries on sales totals.
Detailed Example with Explanations
Let’s consider a practical example where we create various types of views on a Sales table and then demonstrate their usage and benefits.
Step 1: Creating the Sales Table
First, let’s create a Sales table to work with:
CREATE TABLE Sales (
SaleID int PRIMARY KEY,
ProductID int,
Quantity int,
SaleDate datetime
);
This statement creates a Sales table with SaleID, ProductID, Quantity, and SaleDate columns. SaleID is the primary key.
Step 2: Inserting Sample Data
Next, we insert some sample data into the Sales table:
INSERT INTO Sales (SaleID, ProductID, Quantity, SaleDate)
VALUES
(1, 101, 5, '2023-01-01'),
(2, 102, 3, '2023-01-02'),
(3, 101, 2, '2023-01-03');
This statement inserts three rows into the Sales table.
Step 3: Creating a View
Now, let’s create a view to summarize the sales data:
CREATE VIEW SalesSummary AS
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID;
This view summarizes the total quantity of sales for each product.
Step 4: Using the View
With the view created, we can now perform a query to see the summarized data:
SELECT * FROM SalesSummary;
The database retrieves the summarized sales data from the SalesSummary view.
Step 5: Updating Through a View
If the view allows for updates, we can update data in the underlying table through the view:
UPDATE SalesSummary
SET TotalQuantity = TotalQuantity + 1
WHERE ProductID = 101;
Note: This type of update is not typically allowed on aggregated views like SalesSummary. This example is for illustrative purposes; updates on views usually apply to non-aggregated columns.
Step 6: Creating an Indexed View
To improve performance for frequently accessed queries, we create an indexed view:
CREATE VIEW IndexedSalesSummary WITH SCHEMABINDING AS
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID;
CREATE UNIQUE CLUSTERED INDEX idx_sales_summary ON IndexedSalesSummary(ProductID);
This indexed view stores the precomputed results of the summarized sales data, providing faster retrieval.
Step 7: Using the Indexed View
Queries on the indexed view are faster due to the precomputed results:
SELECT * FROM IndexedSalesSummary;
This query retrieves data quickly from the IndexedSalesSummary view, leveraging the index for performance.
Step 8: Dropping a View
If we no longer need a view, we can drop it:
DROP VIEW SalesSummary;
This statement drops the SalesSummary view, removing it from the database without affecting the underlying Sales table.
By understanding and utilizing views, you can simplify complex queries, encapsulate business logic, and improve performance with indexed views. This guide provides a detailed explanation of different types of views, how to create them, and their practical applications.