1. Introduction
In my last article, we explored how to program a SQL function to calculate fiscal quarters. This function simplifies financial data analysis by breaking down periods based on the company’s needs. Today, we will go further by examining how SQL views can optimize your reports and automate complex analyses.
2. Context and Definition
2.1 Definition of an SQL View
An SQL view is a saved query that acts as a virtual table. Unlike traditional tables that store data, a view contains no physical data. It provides a dynamic representation of data stored in other tables based on the criteria defined in the query.
2.2 Advantages of Views:
- Simplification:Encapsulate complex queries into a reusable structure.
- Security: Limit direct access to underlying tables and expose only certain columns or rows.
- Maintenance: Enhance readability and maintenance of SQL queries, especially for frequent transformations or calculations.
Example of creating a view:
CREATE VIEW
[SalesPersonDetails] AS
SELECT
SP.BusinessEntityID AS SalesPersonID, P.FirstName, P.LastName, ST.Name AS Territory
FROM
Sales.SalesPerson AS SP
JOIN
Person.Person AS P ON SP.BusinessEntityID = P.BusinessEntityID
JOIN
Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID;
This view represents a list of information on vendors, completed by joins.
2.3 Difference Between a Simple View and a Complex View
2.3.1 Simple View:
A view that selects columns from a single table, often without complex calculations.
CREATE VIEW [SimpleView] AS
SELECT FirstName, LastName
FROM Person.Person;
This example shows a very simple view listing persons.
2.3.2 Complex View:
Combines several tables and may include joins and aggregations.
Example :
CREATE VIEW [ComplexView] AS
SELECT P.FirstName, P.LastName, SUM(SOD.LineTotal) AS TotalSales
FROM HumanResources.Employee AS E
JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
JOIN Sales.SalesOrderHeader AS SOH ON E.BusinessEntityID = SOH.SalesPersonID
JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
GROUP BY P.FirstName, P.LastName;
2.4 Why Use Views?
- Simplification: Reuse complex queries.
- Reusability: Use the same view in different reports without duplicating code.
- Security: Restrict access to sensitive data.
- Abstraction: Hide underlying tables.
- Performance: Possible improvement with materialized views.
- Business Logic: Centralize calculations.
3. Using Views in Reports
3.1 Preparing Data for Financial Reports
Views pre-aggregate data (sales, margins), simplifying the extraction of coherent data for reports.
CREATE VIEW [AnnualSales] AS
SELECT CustomerID, YEAR(OrderDate) AS OrderYear, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, YEAR(OrderDate);
3.2 Simplifying Complex Calculations
Views encapsulate calculations, making reports faster and avoiding formula repetition.
CREATE VIEW [ProductProfitMargins] AS
SELECT P.ProductID, P.Name, SUM(SOH.TotalDue - SOH.SubTotal) AS Profit
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
GROUP BY P.ProductID, P.Name;
3.3 Automating Reports
Views update data in real-time, making reports always accurate and up to date.
CREATE VIEW [QuarterlySales] AS
SELECT DATEPART(QUARTER, SOH.OrderDate) AS Quarter, YEAR(SOH.OrderDate) AS OrderYear, SUM(SOH.TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader AS SOH
GROUP BY DATEPART(QUARTER, SOH.OrderDate), YEAR(SOH.OrderDate);
4. How to Create and Maintain Views?
4.1 Creation
Creating a view is simple with the CREATE VIEW command.
CREATE VIEW [EmployeeSalaries] AS
SELECT E.BusinessEntityID AS EmployeeID, P.FirstName, P.LastName, EPH.Rate AS Salary
FROM HumanResources.Employee AS E
JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
JOIN HumanResources.EmployeePayHistory AS EPH ON E.BusinessEntityID = EPH.BusinessEntityID
WHERE E.CurrentFlag = 1;
This view includes employees salary data.
4.2 Updating
To modify a view, use the ALTER VIEW command without recreating the reports.
ALTER VIEW [EmployeeSalaries] AS
SELECT E.BusinessEntityID AS EmployeeID, P.FirstName, P.LastName, EPH.Rate AS Salary, D.Name AS Department
FROM HumanResources.Employee AS E
JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
JOIN HumanResources.EmployeePayHistory AS EPH ON E.BusinessEntityID = EPH.BusinessEntityID
JOIN HumanResources.EmployeeDepartmentHistory AS EDH ON E.BusinessEntityID = EDH.BusinessEntityID
JOIN HumanResources.Department AS D ON EDH.DepartmentID = D.DepartmentID
WHERE E.CurrentFlag = 1;
4.3 Impact on Performance
Views are lightweight as they don’t store physical data, but complex views can slow down performance.
- Best Practices:
- Limit unnecessary joins.
- Use indexes on frequently queried columns.
- Consider materialized views for heavy queries.
5. Conclusion
SQL views are a great tool for simplifying reports and automating complex tasks. By encapsulating complex queries into a reusable structure, they enhance both performance and data security. In the next article, we will explore how to use SQL indexes to improve query performance. You will learn how to efficiently use indexes to optimize your databases and speed up query execution.