Part 3 – End-to-End Development Scenarios and Practice Exam Questions

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%)
   --> Design and implement SQL solutions by using AI-assisted tools
      --> Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session

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

Candidates should understand how AI models and MCP-enabled tools work together throughout the SQL development lifecycle—from planning and coding to testing, deployment, and optimization.

End-to-End SQL Development Workflow

The following illustrates a typical workflow for AI-assisted SQL development.

Requirements
      │
      ▼
Developer Prompt
      │
      ▼
GitHub Copilot /
Copilot in Fabric
      │
      ▼
Selected AI Model
      │
      ▼
(Optional)
Invoke MCP Tools
      │
      ▼
Retrieve Context
• Database schema
• Existing procedures
• Documentation
• APIs
• GitHub repository
      │
      ▼
Generate SQL
      │
      ▼
Developer Review
      │
      ▼
Testing
      │
      ▼
Deployment

The AI assists throughout the workflow, but the developer remains responsible for reviewing, validating, and approving the generated solution.

Scenario 1 – Designing a New Database Table

A developer receives the following requirement:

Create a Customer table with auditing columns, primary key, email uniqueness, and indexes.

Prompt

Design a Customer table for Azure SQL Database. Include an identity primary key, audit columns, email uniqueness, and indexes for common lookup operations.

AI Response

The AI generates:

• CREATE TABLE statement
• PRIMARY KEY constraint
• UNIQUE constraint
• DEFAULT values
• indexes
• documentation

The developer reviews:

• naming conventions
• data types
• indexing strategy
• normalization
• storage requirements

Scenario 2 – Creating Stored Procedures

The database already contains 150 tables.

Rather than manually examining the schema, GitHub Copilot uses an approved MCP server.

Developer prompt:

Create a stored procedure that returns all active customers with orders placed within the last 90 days.

Possible MCP interactions:

• Read Customers table
• Read Orders table
• Discover foreign keys
• Retrieve indexes

The AI produces SQL using the actual schema instead of making assumptions.

Scenario 3 – Query Optimization

A report currently takes 22 seconds.

Developer prompt:

Optimize this query for Azure SQL Database.

The reasoning model determines additional information is needed.

Using MCP:

• retrieves execution plan
• retrieves index information
• retrieves statistics
• retrieves row counts

The response includes:

• rewritten SQL
• missing indexes
• parameter sniffing observations
• SARGability improvements
• estimated performance gains

Scenario 4 – Fabric Warehouse Development

A Fabric Warehouse contains several sales tables.

Developer asks:

Explain the warehouse schema and suggest a star schema optimization.

Copilot may retrieve:

• warehouse metadata
• table relationships
• documentation
• semantic model information

The AI can recommend:

• dimension tables
• fact tables
• surrogate keys
• partitioning
• indexing
• warehouse best practices

Scenario 5 – Documentation Generation

Developer prompt:

Document this database.

The AI generates:

• table descriptions
• column summaries
• relationship explanations
• stored procedure documentation
• index summaries
• security notes

This significantly reduces documentation effort.

Scenario 6 – Legacy SQL Refactoring

A SQL Server database contains code written fifteen years ago.

Developer prompt:

Modernize this procedure using current T-SQL best practices.

The AI may recommend:

• TRY…CATCH
• THROW
• CTEs
• window functions
• JSON functions
• simplified joins
• improved naming
• reduced duplication

Scenario 7 – Code Review

Developer prompt:

Review this stored procedure.

The AI evaluates:

• security
• SQL injection risks
• indexing
• readability
• performance
• maintainability

Rather than replacing human review, AI serves as an intelligent reviewer.

Scenario 8 – Database Migration

An organization is migrating SQL Server databases to Azure SQL Database.

Developer prompt:

Identify compatibility issues.

The AI reviews:

• deprecated features
• unsupported syntax
• compatibility level
• indexing recommendations
• Azure SQL best practices

Scenario 9 – Troubleshooting Errors

A deployment fails.

Developer prompt:

Explain this SQL error.

The AI:

• interprets error messages
• explains root causes
• recommends fixes
• suggests troubleshooting steps

Scenario 10 – Learning Existing Code

A new developer joins the team.

Developer prompt:

Explain this stored procedure.

The AI produces:

• high-level summary
• business logic
• table relationships
• parameter explanations
• execution flow

This accelerates onboarding.

Choosing the Appropriate Model

Choosing MCP Tools

Not every prompt requires MCP.

Use MCP when the AI needs:

• live database metadata
• repository contents
• API specifications
• execution plans
• documentation
• schema information

Simple questions such as

What is a clustered index?

generally do not require MCP.

Questions like

Show indexes on my Sales table.

typically do.

Common Development Mistakes

Trusting AI Without Validation

Always review generated SQL.

Using Production Data

Avoid exposing confidential production data unnecessarily.

Ignoring Security

Never assume generated permissions are correct.

Using the Wrong Model

Simple code generation does not always require a reasoning model.

Excessive Permissions

Only enable MCP servers with appropriate permissions.

Skipping Testing

Every generated SQL statement should be:

• reviewed
• tested
• validated

Best Practices

• Write detailed prompts.
• Specify Azure SQL, SQL Server, or Fabric Warehouse when applicable.
• Include schema information.
• Use reasoning models for optimization tasks.
• Use MCP only when external context is beneficial.
• Enable only trusted MCP servers.
• Follow least privilege.
• Review generated SQL before execution.
• Validate performance with execution plans.
• Keep human oversight throughout the development lifecycle.

DP-800 Exam Tips

Candidates should remember:

• AI models generate responses.
• MCP connects AI to external systems.
• Tools perform actions.
• Resources provide information.
• Prompts standardize interactions.
• Authentication determines identity.
• Authorization determines permissions.
• AI operates within the user’s security context.
• Developers remain responsible for validating all AI-generated SQL.

Practice Exam Questions

Question 1

A developer wants GitHub Copilot to recommend missing indexes based on the actual structure of an Azure SQL Database instead of making assumptions.

What should the developer configure?

A. A larger context window only

B. An MCP server that can expose database metadata and indexing tools

C. A faster AI model

D. A local SQL script containing only CREATE TABLE statements

Answer: B

Explanation:

An MCP server enables GitHub Copilot to access live database metadata, including tables, indexes, and statistics. This allows recommendations based on the actual database rather than inferred information. Increasing the context window or switching to a faster model alone does not provide access to external database metadata.

Question 2

A developer needs AI assistance to analyze an execution plan for a query that runs for several minutes.

Which model type is generally the best choice?

A. Fast code-completion model

B. Lightweight autocomplete model

C. Reasoning-focused model

D. Documentation generation model

Answer: C

Explanation:

Execution plan analysis requires complex reasoning and performance optimization capabilities. Reasoning-focused models are designed to analyze execution strategies, identify bottlenecks, and recommend indexing or query improvements.

Question 3

Which MCP component performs operations such as retrieving index information or executing an approved query?

A. Resource

B. Prompt

C. Client

D. Tool

Answer: D

Explanation:

Tools perform actions. Resources provide information, prompts are reusable instructions, and clients host the AI conversation. Retrieving index information or executing approved operations is performed through tools.

Question 4

A developer asks Copilot:

Explain what this stored procedure does.

No external information is required.

What is the most likely outcome?

A. Copilot automatically invokes every available MCP server.

B. Copilot requires administrator approval.

C. Copilot cannot answer without MCP.

D. Copilot answers using the supplied SQL and its language model.

Answer: D

Explanation:

If the prompt includes all necessary information, the AI can respond using its language model without accessing external tools. MCP is used only when additional external context is needed.

Question 5

Why should organizations implement the principle of least privilege for MCP servers?

A. To increase response speed

B. To reduce the number of AI prompts

C. To limit access to only the resources required

D. To improve SQL syntax generation

Answer: C

Explanation:

Least privilege reduces security risks by ensuring that AI assistants and users have access only to the resources necessary to perform their tasks.

Question 6

Which statement best describes the relationship between an AI model and MCP?

A. MCP replaces the language model.

B. MCP generates SQL while the model manages security.

C. The language model generates responses, while MCP enables access to external tools and resources.

D. MCP is another name for GitHub Copilot Chat.

Answer: C

Explanation:

The language model performs reasoning and response generation. MCP provides standardized access to external systems, tools, and resources that supply additional context.

Question 7

A developer wants Copilot to use repository documentation, API specifications, and database schemas when generating SQL.

What feature provides this capability?

A. Larger prompt length

B. Database compatibility level

C. MCP-enabled resources

D. SQL IntelliSense

Answer: C

Explanation:

MCP resources allow AI assistants to access external information such as documentation, schemas, and specifications, improving the relevance and accuracy of generated responses.

Question 8

After AI generates a stored procedure, what should happen next?

A. Deploy directly to production.

B. Trust the AI because it selected a reasoning model.

C. Execute immediately without testing.

D. Review, validate, test, and approve the code before deployment.

Answer: D

Explanation:

AI-generated code should always undergo code review, testing, validation, and approval before being deployed to production.

Question 9

Which scenario is most likely to benefit from an MCP server?

A. Explaining the syntax of a SELECT statement

B. Defining a PRIMARY KEY

C. Retrieving the latest schema and execution statistics from a production database

D. Explaining SQL keywords

Answer: C

Explanation:

Accessing current schemas and execution statistics requires live information from an external system, making MCP the appropriate solution.

Question 10

Why might a developer choose a balanced AI model instead of a fast model?

A. Balanced models are designed to provide stronger reasoning while maintaining good response speed.

B. Balanced models eliminate the need for testing.

C. Balanced models automatically execute SQL.

D. Balanced models replace MCP servers.

Answer: A

Explanation:

Balanced models provide a compromise between speed and reasoning quality, making them well suited for tasks such as stored procedure development, code explanation, and general SQL assistance. They do not replace testing, execute SQL automatically, or substitute for MCP functionality.

Final DP-800 Summary

For this objective, remember these core concepts:

• AI models determine how responses are generated (speed, reasoning, and coding quality).
• MCP determines what additional information or actions the AI can access by connecting to external tools and resources.
• Tools execute approved operations, while resources provide contextual information.
• Authentication identifies the user, and authorization limits what the AI can access on that user’s behalf.
• Developers remain responsible for validating, testing, securing, and approving all AI-generated SQL before deployment.

These concepts are foundational to the DP-800 exam and reflect Microsoft’s direction toward secure, AI-assisted database development.

Go to the DP-800 Exam Prep Hub main page