This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create views

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Views are one of the most commonly used database objects in SQL Server and Azure SQL Database. A view is a virtual table whose contents are defined by a SQL query. Unlike a physical table, a standard view does not store data itself. Instead, it stores the SELECT statement that retrieves data from one or more underlying tables or other views.

Views simplify complex queries, improve security, promote code reuse, and provide an abstraction layer between applications and the underlying database schema. They are frequently used in reporting solutions, business intelligence applications, APIs, and AI-enabled database solutions where consistent access to curated data is essential.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What views are and when to use them
• How to create and modify views
• Simple vs. complex views
• Updatable views
• Schema binding
• Indexed views
• Security considerations
• Best practices for view design

Understanding views is an important skill because they simplify application development while improving maintainability and security.

What Is a View?

A view is a stored SELECT statement that presents data as though it were a table.

Applications can query a view just like a table.

Example:

SELECT *
FROM dbo.vwCustomerOrders;

Although it behaves like a table, the data is retrieved from the underlying objects each time the view is queried (unless the view is indexed).

Benefits of Views

Views provide numerous advantages, including:

• Simplifying complex queries
• Hiding unnecessary columns
• Restricting sensitive data
• Providing a consistent interface for applications
• Improving code maintainability
• Supporting data abstraction
• Enabling reusable business logic
• Simplifying report development

Views help separate the logical database model from the physical implementation.

Creating a View

Basic syntax:

CREATE VIEW dbo.vwCustomers
AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    EmailAddress
FROM dbo.Customers;

Applications can query the view:

SELECT *
FROM dbo.vwCustomers;

Creating a View from Multiple Tables

Views commonly combine data from related tables.

Example:

CREATE VIEW dbo.vwCustomerOrders
AS
SELECT
    c.CustomerID,
    c.FirstName,
    c.LastName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON c.CustomerID = o.CustomerID;

This view simplifies reporting by eliminating the need for repeated JOIN statements.

Simple Views

A simple view references a single table and contains little or no additional logic.

Example:

CREATE VIEW dbo.vwActiveProducts
AS
SELECT
    ProductID,
    ProductName,
    Price
FROM dbo.Products
WHERE IsActive = 1;

Simple views are often updatable.

Complex Views

A complex view may include:

• Multiple tables
• JOINs
• GROUP BY
• Aggregate functions
• UNION
• DISTINCT
• Calculated columns
• Subqueries

Example:

CREATE VIEW dbo.vwMonthlySales
AS
SELECT
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    SUM(TotalAmount) AS MonthlySales
FROM dbo.Orders
GROUP BY
    YEAR(OrderDate),
    MONTH(OrderDate);

Complex views are primarily used for reporting and analytics.

Using Aliases

Column aliases improve readability.

Example:

SELECT
    CustomerID,
    FirstName + ' ' + LastName AS FullName
FROM dbo.Customers;

Meaningful column names make views easier to consume.

Filtering Data

Views frequently filter rows.

Example:

CREATE VIEW dbo.vwOpenOrders
AS
SELECT *
FROM dbo.Orders
WHERE Status = 'Open';

Applications automatically see only open orders.

Using Calculated Columns

Views can include calculated values.

Example:

SELECT
    ProductName,
    UnitPrice,
    Quantity,
    UnitPrice * Quantity AS ExtendedPrice
FROM dbo.OrderDetails;

Calculated columns eliminate repeated calculations across applications.

Modifying a View

Views can be modified using:

ALTER VIEW dbo.vwCustomers
AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    EmailAddress,
    PhoneNumber
FROM dbo.Customers;

ALTER VIEW updates the stored definition while preserving permissions.

Deleting a View

To remove a view:

DROP VIEW dbo.vwCustomers;

Only the view is removed; the underlying tables remain unchanged.

Viewing the Definition of a View

Developers can inspect a view’s definition using:

sp_helptext 'dbo.vwCustomers';

Or:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.vwCustomers'));

This is useful for maintenance and troubleshooting.

Updatable Views

Many views support INSERT, UPDATE, and DELETE operations.

Generally, a view is updatable when it:

• References a single base table
• Does not contain aggregate functions
• Does not contain GROUP BY
• Does not contain DISTINCT
• Does not contain UNION
• Does not contain calculated aggregate results

Example:

UPDATE dbo.vwCustomers
SET EmailAddress = 'newemail@example.com'
WHERE CustomerID = 100;

The underlying table is updated.

WITH CHECK OPTION

WITH CHECK OPTION prevents updates or inserts that would cause rows to no longer satisfy the view’s filter.

Example:

CREATE VIEW dbo.vwActiveEmployees
AS
SELECT *
FROM dbo.Employees
WHERE IsActive = 1
WITH CHECK OPTION;

This ensures that modifications through the view maintain its filtering criteria.

Schema Binding

Views can be created using WITH SCHEMABINDING.

Example:

CREATE VIEW dbo.vwCustomerSales
WITH SCHEMABINDING
AS
SELECT
    CustomerID,
    COUNT_BIG(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerID;

Benefits include:

• Prevents changes to referenced tables that would invalidate the view
• Required for indexed views
• Improves schema stability

When schema binding is used:

• Table names must include the schema name.
• Referenced tables cannot be dropped or modified in ways that break the view until the view is altered or dropped.

Indexed Views

Normally, views do not store data.

An indexed view stores the results of the view physically by creating a unique clustered index on the view.

Benefits:

• Faster query performance
• Reduced computation for expensive aggregations
• Useful for reporting workloads

Requirements include:

• WITH SCHEMABINDING
• Deterministic expressions
• Additional SQL Server restrictions
• A unique clustered index created first

Example:

CREATE UNIQUE CLUSTERED INDEX IX_vwCustomerSales
ON dbo.vwCustomerSales(CustomerID);

Keep in mind that indexed views can improve read performance but may increase the cost of INSERT, UPDATE, and DELETE operations because the indexed view must also be maintained.

Views and Security

Views are commonly used to restrict access to sensitive information.

Example:

Base table:

EmployeeID
Name
Salary
SocialSecurityNumber

View:

EmployeeID
Name

Users receive access to the view instead of the underlying table.

This supports the principle of least privilege by exposing only the necessary columns and rows.

Nested Views

A view can reference another view.

Example:

Orders
   │
vwOpenOrders
   │
vwRecentOpenOrders

Although supported, excessive nesting can:

• Reduce performance
• Complicate troubleshooting
• Make execution plans harder to understand

Microsoft generally recommends minimizing unnecessary layers of nested views.

Limitations of Views

Standard views:

• Do not normally store data
• Cannot accept parameters (use table-valued functions if parameters are required)
• May become invalid if underlying objects change (unless schema binding is used)
• May not always improve performance
• Can become difficult to maintain if overly complex

Views vs. Tables

Views vs. Stored Procedures

AI-Enabled Database Scenarios

Views are valuable in AI-enabled database solutions because they provide a consistent and secure layer over operational data.

Common uses include:

• Creating curated datasets for machine learning
• Exposing only relevant columns for AI models
• Simplifying feature engineering queries
• Combining business data with vector metadata
• Preparing reporting datasets for model evaluation
• Restricting sensitive information before AI processing

Views help ensure that AI applications consume consistent, high-quality data while reducing the complexity of application queries.

Best Practices

• Use meaningful and consistent naming conventions (for example, vwCustomerOrders).
• Keep views focused on a single business purpose.
• Avoid unnecessary nested views.
• Use aliases to improve readability.
• Use WITH SCHEMABINDING when appropriate, especially for indexed views.
• Consider indexed views only when query performance benefits outweigh maintenance costs.
• Grant permissions to views instead of base tables when restricting data access.
• Document complex business logic contained within views.
• Periodically review execution plans to ensure views are not introducing unnecessary overhead.

Common Exam Tips

For the DP-800 exam, remember these key points:

• A view is a stored SELECT statement that behaves like a virtual table.
• Standard views do not physically store data.
• Indexed views physically store data after a unique clustered index is created.
• ALTER VIEW modifies an existing view while preserving permissions.
• WITH CHECK OPTION ensures rows modified through a view continue to satisfy the view’s filter.
• WITH SCHEMABINDING prevents incompatible schema changes and is required for indexed views.
• Simple views are often updatable; views containing aggregates, GROUP BY, DISTINCT, or UNION generally are not.
• Views are commonly used to simplify queries and improve security.

Practice Exam Questions

Question 1

A developer wants to simplify a frequently used query that joins the Customers and Orders tables without duplicating the JOIN logic throughout an application. Which database object should be created?

A. A stored procedure

B. A trigger

C. A view

D. A sequence

Answer: C

Explanation: A view stores a SELECT statement that can join multiple tables, allowing applications to query the view instead of repeatedly writing the same JOIN logic.

Question 2

Which statement correctly describes a standard SQL Server view?

A. It always stores its data physically.

B. It stores the SELECT statement that defines the virtual table.

C. It automatically creates a clustered index.

D. It requires a PRIMARY KEY.

Answer: B

Explanation: A standard view stores only its definition (the SELECT statement). Data is retrieved from the underlying tables each time the view is queried.

Question 3

A database administrator wants to prevent changes to the underlying tables that would invalidate a view. Which option should be used when creating the view?

A. WITH CHECK OPTION

B. ENCRYPTION

C. WITH SCHEMABINDING

D. RECOMPILE

Answer: C

Explanation: WITH SCHEMABINDING binds the view to the schema of the referenced tables, preventing changes that would invalidate the view.

Question 4

Which statement about indexed views is correct?

A. They require a unique clustered index before additional indexes can be created.

B. They automatically update only once per day.

C. They cannot reference aggregate functions.

D. They never increase the cost of data modifications.

Answer: A

Explanation: An indexed view requires WITH SCHEMABINDING and a unique clustered index as the first index. Data modifications to underlying tables also update the indexed view.

Question 5

A view is defined with the following clause:

WITH CHECK OPTION

What is the primary purpose of this clause?

A. To encrypt the view definition.

B. To ensure that INSERT and UPDATE operations through the view continue to satisfy the view’s filtering criteria.

C. To improve query performance.

D. To automatically rebuild indexes.

Answer: B

Explanation: WITH CHECK OPTION prevents modifications through the view that would produce rows no longer visible through that view.

Question 6

Which characteristic generally allows a view to be updatable?

A. It contains GROUP BY and aggregate functions.

B. It contains a UNION operator.

C. It references a single base table without aggregate operations.

D. It contains DISTINCT and calculated aggregates.

Answer: C

Explanation: Simple views that reference a single base table and avoid constructs such as GROUP BY, DISTINCT, and UNION are often updatable.

Question 7

Which statement best describes an indexed view?

A. It always executes more slowly than a standard view.

B. It physically stores the results of the view after a unique clustered index is created.

C. It can only reference one table.

D. It cannot be queried using SELECT statements.

Answer: B

Explanation: Indexed views materialize their data through a unique clustered index, improving performance for certain read-heavy workloads.

Question 8

Why are views commonly used to improve database security?

A. They automatically encrypt data.

B. They replace the need for permissions.

C. They allow administrators to expose only selected columns and rows while restricting access to the underlying tables.

D. They prevent all updates to data.

Answer: C

Explanation: Views can limit the data users see, making them an effective way to implement the principle of least privilege.

Question 9

A developer modifies an existing view by using ALTER VIEW. What happens to the permissions already granted on that view?

A. They are automatically removed.

B. They are transferred to the underlying tables.

C. They are preserved.

D. They are converted to DENY permissions.

Answer: C

Explanation: Using ALTER VIEW changes the view definition while preserving existing permissions on the view.

Question 10

Which statement best explains why views are useful in AI-enabled database solutions?

A. They automatically generate machine learning models.

B. They replace the need for indexes.

C. They eliminate all data transformations.

D. They provide consistent, reusable, and secure datasets that simplify AI data preparation.

Answer: D

Explanation: Views provide a stable abstraction layer that exposes curated, consistent datasets while helping to restrict sensitive data, making them valuable for analytics and AI workloads.

Go to the DP-800 Exam Prep Hub main page