This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Integrate SQL solutions with Azure services
      --> Expose database objects, stored procedures, and views, including GraphQL relationships

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

Modern applications rarely communicate directly with a database. Instead, they interact with APIs that expose only the data and operations that applications require. Microsoft’s Data API builder (DAB) provides a secure and efficient way to expose Azure SQL Database, Azure SQL Managed Instance, SQL Server, and Azure Database for PostgreSQL as REST and GraphQL APIs without requiring developers to build custom API services.

One of the primary responsibilities of a SQL AI Developer is deciding which database objects should be exposed, how they should be exposed, and how relationships between entities should be represented, particularly in GraphQL.

For the DP-800 exam, candidates should understand how to expose:

• Tables
• Views
• Stored procedures
• Relationships between entities
• GraphQL navigation
• REST resources
• Security considerations
• Performance considerations

Why Expose Database Objects?

Instead of allowing applications to connect directly to a database, organizations commonly expose selected database objects through APIs because APIs provide:

• Better security
• Controlled access
• Versioning
• Authentication
• Authorization
• Business logic abstraction
• Simplified client development

Rather than allowing direct SQL access, applications interact with HTTP endpoints such as:

GET /api/Products

or GraphQL queries like:

query {
  products {
    ProductID
    Name
    Price
  }
}

Objects That Can Be Exposed

Microsoft Data API builder can expose several database object types.

1. Tables

Tables are the most common objects exposed.

Example:

Products
Customers
Orders
Employees

Each table becomes an entity.

Example DAB configuration:

{
  "entities": {
    "Products": {
      "source": "Products"
    }
  }
}

REST endpoints generated:

GET /api/Products
POST /api/Products
PATCH /api/Products
DELETE /api/Products

GraphQL automatically generates:

products
product_by_pk

and corresponding mutations.

2. Views

Views provide a secure way to expose pre-filtered or joined data.

Example:

vwSalesSummary

Instead of exposing many tables, clients consume the view.

Benefits include:

• Simplified queries
• Hidden table structure
• Security abstraction
• Read-only reporting

Example:

CustomerName
OrderCount
TotalSales

instead of requiring joins.

Views are especially useful for reporting applications.

3. Stored Procedures

Stored procedures expose business logic rather than raw tables.

Example:

EXEC usp_CreateOrder

Instead of allowing clients to insert rows manually.

Advantages include:

• Validation
• Business rules
• Transactions
• Consistent processing

Data API builder supports stored procedures as API operations.

Example REST endpoint:

POST /api/CreateOrder

Why Use Stored Procedures?

Stored procedures provide:

• Better security
• Centralized business rules
• Reduced network traffic
• Transaction handling
• Parameter validation

Example:

Instead of:

Insert Order
Insert Items
Update Inventory
Calculate Discount
Commit Transaction

The application calls:

CreateOrder()

The stored procedure performs every operation safely.

Exposing Views vs Tables

Exposing Stored Procedures

Stored procedures typically become REST POST operations because they execute actions.

Example:

POST
/api/ProcessPayment

Input:

{
   "OrderID":1054
}

The procedure performs the transaction.

GraphQL Relationships

One of GraphQL’s greatest advantages is navigating relationships between entities.

Instead of making several REST calls:

Customers
Orders
OrderDetails

GraphQL can retrieve all related information in one request.

Example:

query {
  customers {
    CustomerName
    orders {
        OrderID
        OrderDate
        orderDetails {
             ProductName
             Quantity
        }
    }
}

GraphQL traverses relationships automatically.

Understanding Relationships

Suppose the database contains:

Customers
Orders
Products
OrderDetails

Relationships:

Customer
   |
   | 1:M
   |
Orders
   |
   | 1:M
   |
OrderDetails
   |
   | M:1
   |
Products

GraphQL follows these relationships naturally.

One-to-Many Relationships

Example:

Customer

↓

Orders

Example query:

query{
 customers{
   CustomerName
   orders{
      OrderID
      OrderDate
   }
 }
}

The response includes each customer’s orders.

Many-to-One Relationships

Example:

OrderDetails

↓

Product

query{
 orderDetails{
    Quantity
    product{
       Name
       Price
    }
 }
}

Many-to-Many Relationships

Many-to-many relationships are typically implemented through junction tables.

Example:

Students
Courses
StudentCourses

GraphQL can expose navigation through the junction table.

REST vs GraphQL for Relationships

REST

GET Customers
GET Orders
GET OrderDetails

Multiple requests required.

GraphQL

One query retrieves everything.

Advantages:

• Reduced network traffic
• Less over-fetching
• Less under-fetching
• Better performance

Relationship Configuration in Data API Builder

Relationships are defined inside the configuration.

Example concept:

Customers
   hasMany
Orders

and

Orders
belongsTo
Customers

This allows nested GraphQL queries.

CRUD Support

Depending on configuration, exposed entities may support:

Create

POST

Read

GET

Update

PUT
PATCH

Delete

DELETE

Not every entity must support every operation.

For example:

Views

Read Only

Tables

Read + Write

Restricting Exposed Objects

Best practice is not to expose every table.

Expose only:

• Required tables
• Required views
• Required procedures

Avoid exposing:

• Audit tables
• Internal configuration
• Security tables
• Temporary tables
• Logging tables

Least privilege always applies.

Security Considerations

When exposing database objects:

• Require HTTPS
• Use Microsoft Entra authentication
• Apply least privilege
• Use role-based authorization
• Expose only necessary objects
• Validate procedure parameters
• Avoid exposing sensitive columns
• Audit endpoint usage

Performance Considerations

Good API design includes:

• Return only needed fields
• Use pagination
• Cache reference data
• Optimize SQL queries
• Index frequently queried columns
• Avoid unnecessary nested GraphQL queries
• Use views for complex reporting

Common DP-800 Exam Tips

Know when to expose:

Summary

For the DP-800 exam, you should understand that Data API builder can expose tables, views, and stored procedures as secure REST and GraphQL endpoints. Tables are commonly used for CRUD operations, views simplify reporting and hide underlying schemas, and stored procedures encapsulate business logic and transactional operations. GraphQL relationships allow clients to traverse related entities in a single request, reducing network calls and simplifying application development. Developers should expose only the objects required by the application, apply least-privilege security principles, and optimize endpoints for performance and maintainability.

Practice Exam Questions

Question 1

Your organization wants external applications to retrieve product information without exposing the underlying table structure or requiring complex joins. Which database object should you expose?

A. A view

B. A database trigger

C. A SQL Agent job

D. A temporary table

Correct Answer:

A. A view

Explanation

Views present a simplified, controlled representation of data by encapsulating joins and filters. They hide the underlying schema, making them ideal for reporting and read-only access. Triggers, SQL Agent jobs, and temporary tables are not intended to expose data to applications.

Question 2

Which type of database object is best suited for encapsulating business logic that performs multiple database operations within a single transaction?

A. A view

B. A stored procedure

C. A synonym

D. An index

Correct Answer:

B. A stored procedure

Explanation

Stored procedures centralize business logic, validate inputs, manage transactions, and execute multiple SQL statements as a single unit of work. Views are primarily for querying data, while synonyms and indexes do not execute business logic.

Question 3

An application uses GraphQL to retrieve customer information and all associated orders in a single request.

Which GraphQL capability makes this possible?

A. Automatic indexing

B. HTTP caching

C. Entity relationships

D. SQL triggers

Correct Answer:

C. Entity relationships

Explanation

GraphQL relationships allow clients to traverse related entities through nested queries, enabling retrieval of customers and their orders in a single request. This is one of GraphQL’s primary advantages over traditional REST APIs.

Question 4

A developer exposes a database table through Data API builder and wants clients to retrieve records using REST.

Which HTTP method should clients use?

A. DELETE

B. PATCH

C. POST

D. GET

Correct Answer:

D. GET

Explanation

REST uses the GET method to retrieve resources. POST creates resources, PATCH updates existing resources, and DELETE removes resources.

Question 5

Which object is most appropriate for exposing aggregated sales totals without allowing users to modify the underlying data?

A. A stored procedure

B. A table

C. A view

D. A trigger

Correct Answer:

C. A view

Explanation

Views are commonly used to expose aggregated or summarized information while hiding the complexity of the underlying tables. Many reporting views are read-only, preventing accidental modifications.

Question 6

A Data API builder configuration includes only the Products and Categories entities.

What happens if a client attempts to access the Employees table?

A. The request succeeds because all tables are exposed automatically.

B. The table is exposed only through GraphQL.

C. The request fails because Employees is not configured as an exposed entity.

D. Data API builder creates the endpoint automatically.

Correct Answer:

C. The request fails because Employees is not configured as an exposed entity.

Explanation

Data API builder exposes only the entities explicitly defined in its configuration. Objects not configured remain inaccessible through both REST and GraphQL endpoints.

Question 7

Why should developers avoid exposing every database table through REST or GraphQL endpoints?

A. Because GraphQL cannot access multiple tables.

B. To follow the principle of least privilege and reduce security risks.

C. Because Data API builder supports only five entities.

D. To improve SQL syntax compatibility.

Correct Answer:

B. To follow the principle of least privilege and reduce security risks.

Explanation

Exposing only required objects reduces the attack surface, protects sensitive data, and aligns with security best practices. Internal, audit, configuration, and security tables should generally remain inaccessible.

Question 8

Which GraphQL feature reduces the need for multiple REST API calls when retrieving related data?

A. Stored procedures

B. Pagination

C. HTTP status codes

D. Nested queries using relationships

Correct Answer:

D. Nested queries using relationships

Explanation

GraphQL allows nested queries that follow entity relationships, enabling clients to retrieve related objects in a single request. This minimizes network traffic and simplifies application development.

Question 9

Which database object is generally the best choice for exposing an operation that validates inventory, creates an order, updates stock levels, and commits the transaction?

A. A stored procedure

B. A view

C. A nonclustered index

D. A foreign key

Correct Answer:

A. A stored procedure

Explanation

Stored procedures encapsulate complex business processes, ensure transactional consistency, and centralize business rules. Views and indexes cannot perform transactional workflows.

Question 10

A GraphQL query retrieves customer information along with orders and order details.

What is the primary benefit of this approach compared to making several REST requests?

A. SQL Server automatically creates indexes.

B. Database permissions are no longer required.

C. Authentication becomes optional.

D. Multiple related resources can be retrieved in a single request, reducing network overhead.

Correct Answer:

D. Multiple related resources can be retrieved in a single request, reducing network overhead.

Explanation

GraphQL enables clients to retrieve exactly the required data—including related entities—in a single query. This reduces round trips, minimizes over-fetching and under-fetching, and often improves application performance.

Go to the DP-800 Exam Prep Hub main page