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:
ProductsCustomersOrdersEmployees
Each table becomes an entity.
Example DAB configuration:
{ "entities": { "Products": { "source": "Products" } }}
REST endpoints generated:
GET /api/ProductsPOST /api/ProductsPATCH /api/ProductsDELETE /api/Products
GraphQL automatically generates:
productsproduct_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:
CustomerNameOrderCountTotalSales
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 OrderInsert ItemsUpdate InventoryCalculate DiscountCommit Transaction
The application calls:
CreateOrder()
The stored procedure performs every operation safely.
Exposing Views vs Tables
| Tables | Views |
|---|---|
| Raw data | Processed data |
| Often updateable | Often read-only |
| Complete schema | Simplified schema |
| Less abstraction | Greater abstraction |
| Better for CRUD | Better for reporting |
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:
CustomersOrdersOrderDetails
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:
CustomersOrdersProductsOrderDetails
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:
StudentsCoursesStudentCourses
GraphQL can expose navigation through the junction table.
REST vs GraphQL for Relationships
REST
GET CustomersGET OrdersGET 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 hasManyOrders
and
OrdersbelongsToCustomers
This allows nested GraphQL queries.
CRUD Support
Depending on configuration, exposed entities may support:
Create
POST
Read
GET
Update
PUTPATCH
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:
| Object | Typical Use |
|---|---|
| Table | CRUD operations |
| View | Reporting and simplified queries |
| Stored Procedure | Business logic and transactions |
| GraphQL Relationship | Nested related data |
| REST Endpoint | Resource-oriented operations |
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
