DP-900: understand and use views in Azure SQL Database

I will teach the skill of creating and using views in Azure SQL Database — a topic useful for DP-900 because it demonstrates understanding of abstraction and query optimization in relational data, and is practical for designing secure and manageable cloud solutions.

What you need to know

A view is a stored query that appears to the user as a virtual table. It does not store data (except if it is an indexed/materialized view, which is less common in Azure SQL Database), but it simplifies access to complex data, encapsulates logic and can enforce security by exposing only specific columns/rows.

Conceptual example: suppose two tables, Customers and Orders. To obtain the total orders per customer, you can create a view that performs the join and aggregation. Whoever uses the view does not need to know the details of the join or the aggregate functions.

CREATE VIEW Sales.CustomerOrderTotals AS
SELECT c.CustomerID,
       c.Name,
       SUM(o.TotalAmount) AS TotalOrders
FROM dbo.Customers c
JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;

After creation, you can query: SELECT * FROM Sales.CustomerOrderTotals WHERE TotalOrders > 1000;

How it works / In practice

Practical points and steps to use views in Azure SQL Database:

1. Model the need: define whether the view is to simplify queries, enforce security rules (expose only certain columns) or to reconcile schema between applications.
2. Write the base query: test the SELECT query that will define the view, optimizing joins and filters before creating the view.
3. Create the view: use CREATE VIEW schema.Name AS <select>. Preferably qualify names with schema (dbo, Sales...).
4. Use the view as if it were a table: you can do SELECT, JOIN and even apply filters. Note that performance depends on the underlying query — the view does not automatically reduce execution costs.
5. Updates via view: some views are updatable (can be used in INSERT/UPDATE/DELETE) if they meet rules (for example, a single table without aggregates). If the view is not updatable, use stored procedures or DML directly on the base tables.
6. Security: apply permissions on the view (GRANT SELECT ON Sales.CustomerOrderTotals TO ReportRole) to limit access to sensitive columns in the base tables.
7. Performance verification: use Query Store and the Azure SQL diagnostic tools to analyze execution plans of the query the view encapsulates and avoid performance surprises.

-- Example: grant access only to the view
CREATE ROLE reporting_role;
GRANT SELECT ON Sales.CustomerOrderTotals TO reporting_role;
-- It is not necessary to give direct access to the underlying tables

Common mistakes

1) Trusting that a view improves performance by itself: a view is only an abstraction layer; the engine still executes the underlying query. It is necessary to review execution plans.

2) Creating views with SELECT *: this can hide schema changes and bring unnecessary columns, affecting network consumption and applications. Explicitly define columns.

3) Using complex and nested views without considering data updates and maintenance: views with aggregations and complex joins can make debugging harder and increase maintenance cost. Document and test each level.

How to practice

Practical steps for exercises with official resources:

1. Create an Azure SQL Database instance (you can use the free/limited-time tier or a local development environment with SQL Server Express to learn the syntax).
2. Create sample tables (Customers, Orders, Products), populate with test data and write SELECT queries that synthesize information.
3. Turn those queries into views, apply permissions and test read and write scenarios (when applicable).
4. Analyze execution plans and use Query Store to compare performance of direct queries vs via view.

For alignment with the DP-900 exam, also practice with the official resources: take the Microsoft official Practice Assessment free and consult the official study guide (both free). These resources help verify knowledge of the areas measured by the exam.

In summary

• A view is a virtual table that encapsulates a query — useful to simplify access and apply security.
• Views do not automatically improve performance; optimize the underlying query and use Azure SQL diagnostic tools.
• Avoid SELECT * in views; specify columns and document the purpose of each view.
• Practice in Azure SQL Database, use Query Store and experiment with granting permissions on views instead of base tables.