Tag: Databases

Database Administration, Databases, DP-800, Microsoft Certification August 1, 2026

Implement secrets management (DP-800 Exam Prep)

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%)
   --> Implement CI/CD by using SQL Database Projects
      --> Implement secrets management

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 database applications rarely operate in isolation. They connect to databases, Azure services, AI models, storage accounts, APIs, messaging services, and monitoring tools. Each connection typically requires credentials such as passwords, connection strings, API keys, certificates, tokens, or managed identities.

One of the most common security mistakes is storing these secrets directly in application code, SQL scripts, configuration files, or source control repositories. Modern DevOps practices eliminate this risk by implementing centralized secrets management, ensuring that sensitive information is securely stored, rotated, audited, and accessed only by authorized applications and users.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how secrets management integrates with SQL Database Projects, Azure DevOps, GitHub, Azure Key Vault, Managed Identity, Microsoft Entra ID, CI/CD pipelines, and Azure SQL Database deployments.

What Are Secrets?

A secret is any sensitive information used to authenticate or authorize access to a resource.

Examples include:

Database passwords
SQL authentication credentials
Azure Storage account keys
Azure OpenAI API keys
Azure AI Search API keys
Connection strings
Service Principal secrets
OAuth client secrets
Certificates
Personal Access Tokens (PATs)
SAS tokens
Encryption keys

Secrets should always be protected because unauthorized disclosure can compromise systems and data.

Why Secrets Management Is Important

Poor secrets management can result in:

Unauthorized database access
Data breaches
Credential theft
Service impersonation
Compliance violations
Accidental exposure in public repositories
Unauthorized AI model usage
Financial loss

Proper secrets management helps organizations achieve:

Least privilege
Secure authentication
Regulatory compliance
Credential rotation
Centralized auditing
Simplified administration

Common Security Risks

Common mistakes include storing secrets in:

			
{
    "ConnectionString":
    "Server=myserver;
    User ID=admin;
    Password=P@ssword123!"
}

		

			
CREATE LOGIN appuser
WITH PASSWORD='MyPassword!';

AzureOpenAIKey=abc123xyz

These files often become part of Git repositories and remain permanently visible in version history, even if later deleted.

Principles of Secrets Management

Microsoft recommends the following principles:

Never hard-code secrets.
Never store secrets in source control.
Use centralized secret stores.
Use managed identities whenever possible.
Rotate secrets regularly.
Grant only required permissions.
Audit secret access.
Automate secret retrieval.
Encrypt secrets both at rest and in transit.

Azure Key Vault

Azure Key Vault is Microsoft’s centralized secrets management service.

It securely stores:

Passwords
Keys
Certificates
Tokens
Connection strings
API keys

Applications retrieve secrets at runtime rather than storing them locally.

Benefits include:

Centralized management
Encryption
Access policies
Role-Based Access Control (RBAC)
Secret versioning
Automatic rotation support
Auditing
High availability

Types of Objects in Azure Key Vault

Azure Key Vault stores three object types:

Secrets

Examples:

Passwords
API keys
Connection strings

Keys

Used for:

Encryption
Digital signatures
Key management

Certificates

Used for:

TLS authentication
Client authentication
Secure communications

Secret Lifecycle

Typical lifecycle:

			
Create Secret
↓
Store in Key Vault
↓
Grant Access
↓
Retrieve During Execution
↓
Rotate
↓
Update Applications
↓
Retire Old Version

		

Secret Versioning

Azure Key Vault automatically versions secrets.

Example:

			
DatabasePassword
Version 1
↓
Version 2
↓
Version 3

		

Applications can:

Use the latest version
Pin to a specific version
Rotate without downtime

Managed Identity

Whenever possible, Microsoft recommends using Managed Identity instead of secrets.

Managed Identity eliminates:

Passwords
Client secrets
Credential rotation

Instead:

Azure automatically authenticates the workload.

Supported services include:

Azure SQL Database
Azure App Service
Azure Functions
Azure Container Apps
Azure Kubernetes Service
Azure Virtual Machines
Azure Data Factory
Microsoft Fabric services (where supported)

Types of Managed Identity

System-Assigned Managed Identity

Characteristics:

One identity
Lifecycle tied to the Azure resource
Automatically deleted with the resource

User-Assigned Managed Identity

Characteristics:

Independent Azure resource
Shared across multiple services
Longer lifecycle
Easier identity reuse

Microsoft Entra ID Authentication

Microsoft recommends using Microsoft Entra ID authentication rather than SQL logins whenever possible.

Benefits include:

Centralized identity management
Multi-factor authentication
Conditional Access
Passwordless authentication
Single Sign-On
Managed Identity integration

Secrets in SQL Database Projects

SQL Database Projects should never contain:

Passwords
API keys
Tokens
Production connection strings

Instead they should contain:

Schema definitions
Stored procedures
Functions
Views
Security objects
Build configurations

Secrets should be injected during deployment.

Secrets in Azure DevOps

Azure DevOps supports secure secret storage through:

Variable Groups
Secret Variables
Azure Key Vault integration
Service Connections
Managed Identity (supported services)

Example pipeline:

			
Build
↓
Retrieve Secret
↓
Deploy DACPAC
↓
Remove Secret From Memory

		

Secrets remain encrypted throughout execution.

Secrets in GitHub

GitHub provides encrypted GitHub Secrets.

Secrets can be defined at:

Repository level
Environment level
Organization level

Examples:

SQL_PASSWORD
AZURE_CLIENT_ID
OPENAI_API_KEY

GitHub Actions retrieves them securely during workflow execution.

GitHub Actions Example

			
env:
  SQL_PASSWORD: ${{ secrets.SQL_PASSWORD }}

The actual password never appears in the workflow file.

Azure DevOps Example

			
variables:
- group: ProductionSecrets

The pipeline references the secure variable group rather than storing credentials.

Secret Rotation

Secrets should be rotated periodically.

Reasons include:

Compliance
Reduced exposure
Personnel changes
Compromised credentials
Security policies

Rotation process:

			
Create New Secret
↓
Update Applications
↓
Validate
↓
Disable Old Secret
↓
Delete Old Secret

		

Access Control

Access should follow the Principle of Least Privilege.

Applications receive:

Only required permissions
Only required secrets
Only for required duration

Avoid granting:

Vault Administrator
Owner
Full secret access

Unless absolutely necessary.

RBAC vs Access Policies

Azure Key Vault supports:

Azure RBAC

Uses Azure role assignments.

Examples:

Key Vault Secrets User
Key Vault Administrator
Key Vault Reader

Recommended for new deployments.

Access Policies

Older permission model.

Still supported but Microsoft recommends RBAC for most new implementations.

Secret Auditing

Organizations should monitor:

Secret retrieval
Failed access attempts
Secret updates
Secret deletion
Permission changes

Azure Monitor and Azure Activity Logs provide auditing capabilities.

CI/CD Pipeline Integration

Typical deployment:

			
Developer
↓
GitHub
↓
Pull Request
↓
Build
↓
Retrieve Secrets
↓
Deploy DACPAC
↓
Azure SQL Database

		

Secrets remain outside source control throughout the deployment.

Environment-Specific Secrets

Different environments use different secrets.

Example:

Environment	Database
Development	Dev SQL
Test	Test SQL
Production	Production SQL

Each environment references its own Key Vault or secret store.

Secure Connection Strings

Instead of:

			
Server=myserver;
User=admin;
Password=P@ssword123

Use:

Managed Identity
Microsoft Entra authentication
Secret references
Azure Key Vault retrieval

Preventing Secret Leakage

Organizations should:

Enable secret scanning
Use repository scanning
Review pull requests
Block committed secrets
Rotate exposed credentials immediately
Monitor repositories continuously

GitHub Advanced Security and Microsoft Defender for DevOps can detect exposed credentials.

Common Mistakes

Avoid:

Hard-coded passwords
SQL logins embedded in code
API keys inside scripts
Secrets in Git repositories
Emailing passwords
Sharing credentials among developers
Using production secrets in development
Long-lived credentials
Ignoring secret rotation

Best Practices

Microsoft recommends:

Use Azure Key Vault.
Prefer Managed Identity over passwords.
Use Microsoft Entra authentication.
Never commit secrets to Git.
Rotate secrets regularly.
Enable auditing.
Apply least privilege.
Separate secrets by environment.
Automate secret retrieval.
Protect CI/CD pipelines.
Use RBAC for Key Vault authorization.
Monitor secret access continuously.

DP-800 Exam Tips

Remember these important points:

Azure Key Vault is Microsoft’s preferred centralized secrets management solution.
Managed Identity is preferred over passwords or client secrets whenever supported.
SQL Database Projects should never contain secrets.
GitHub Secrets and Azure DevOps Secret Variables securely provide credentials during pipeline execution.
Secrets should be retrieved at runtime rather than stored in source code.
Secret rotation reduces the risk of credential compromise.
Microsoft Entra ID provides modern authentication with support for passwordless and managed identities.
Apply least privilege to secret access.
Enable auditing and monitoring for secret usage.
Never commit connection strings containing passwords to source control.

Practice Exam Questions

Question 1

A development team wants to eliminate database passwords from its Azure-hosted application. Which authentication method should be used whenever possible?

A. SQL Authentication with a strong password

B. Windows Authentication over VPN

C. Managed Identity

D. Shared administrator account

Answer: C

Explanation: Managed Identity allows Azure resources to authenticate to supported services without storing passwords or client secrets, reducing administrative overhead and improving security.

Question 2

Which Azure service is specifically designed to centrally store passwords, certificates, keys, and connection strings?

A. Azure Key Vault

B. Azure Monitor

C. Azure Storage

D. Azure Policy

Answer: A

Explanation: Azure Key Vault provides secure storage, versioning, access control, auditing, and rotation capabilities for secrets, keys, and certificates.

Question 3

A SQL Database Project needs to connect to an Azure SQL Database during deployment. Where should the production connection string password be stored?

A. In Azure Key Vault or a secure pipeline secret store

B. In a README file

C. In the SQL project file

D. In the source code comments

Answer: A

Explanation: Production credentials should never be committed to source control. They should be stored securely in Azure Key Vault or pipeline secret stores such as GitHub Secrets or Azure DevOps Secret Variables.

Question 4

Which practice represents the greatest security risk?

A. Using Microsoft Entra ID authentication

B. Storing passwords in Azure Key Vault

C. Using Managed Identity

D. Hard-coding API keys in application source code

Answer: D

Explanation: Hard-coded secrets are easily exposed through source control, backups, or application binaries and are considered a major security vulnerability.

Question 5

Why should secrets be rotated on a regular basis?

A. To reduce the risk associated with compromised credentials

B. To improve SQL query performance

C. To reduce storage costs

D. To simplify branching strategies

Answer: A

Explanation: Regular rotation limits the usefulness of compromised credentials and helps organizations meet compliance and security requirements.

Question 6

Which GitHub feature securely provides sensitive values to GitHub Actions workflows?

A. Repository Wiki

B. GitHub Issues

C. GitHub Releases

D. GitHub Secrets

Answer: D

Explanation: GitHub Secrets securely stores encrypted values that workflows can access during execution without exposing them in source code.

Question 7

A company wants applications to authenticate to Azure SQL Database using centralized identity management, Multi-Factor Authentication, and Conditional Access policies. Which authentication method best supports these requirements?

A. SQL logins

B. Microsoft Entra ID authentication

C. Shared local accounts

D. Anonymous authentication

Answer: B

Explanation: Microsoft Entra ID provides centralized authentication with advanced security features including MFA, Conditional Access, Single Sign-On, and integration with Managed Identity.

Question 8

Which authorization principle should be applied when granting applications access to secrets?

A. Full administrative access

B. Read and write access for all developers

C. Principle of Least Privilege

D. Anonymous access

Answer: C

Explanation: Applications should receive only the permissions required to perform their tasks, reducing the potential impact of compromised identities.

Question 9

What is the primary benefit of storing secrets outside a SQL Database Project?

A. Faster database indexing

B. Reduced network latency

C. Automatic SQL optimization

D. Sensitive credentials remain protected and can be managed independently of application code

Answer: D

Explanation: Separating secrets from application code improves security, supports credential rotation, simplifies compliance, and prevents accidental exposure through source control.

Question 10

A CI/CD pipeline retrieves a database password from Azure Key Vault immediately before deploying a DACPAC. What is the primary advantage of this approach?

A. It permanently stores the password inside the DACPAC.

B. It eliminates the need for authentication.

C. It allows credentials to be securely retrieved at deployment time without storing them in source control.

D. It improves query execution plans.

Answer: C

Explanation: Retrieving secrets during deployment keeps credentials out of source control and build artifacts while allowing secure, centralized management and rotation of sensitive information.

Go to the DP-800 Exam Prep Hub main page

Data Development, Data Governance, Databases, DP-800, Microsoft Certification August 1, 2026

Manage branching, pull requests, and conflict resolution (DP-800 Exam Prep)

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%)
   --> Implement CI/CD by using SQL Database Projects
      --> Manage branching, pull requests, and conflict resolution

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 database development follows the same DevOps practices used in application development. Rather than making changes directly in production databases, developers define database objects in SQL Database Projects, store them in Git repositories, collaborate through branches, validate changes with pull requests (PRs), and deploy them using automated CI/CD pipelines.

Branching strategies, code reviews, and conflict resolution are critical to maintaining database quality and enabling multiple developers to work on the same project without interfering with one another. For the DP-800: Developing AI-Enabled Database Solutions exam, candidates should understand how SQL Database Projects integrate with GitHub and Azure DevOps, how branches support parallel development, how pull requests improve code quality, and how merge conflicts are identified and resolved.

Why Branching Matters

Without branching, every developer would modify the same code base simultaneously, leading to:

Frequent overwriting of changes
Lost work
Deployment failures
Unstable builds
Difficult troubleshooting

Branching allows developers to:

Work independently
Develop multiple features simultaneously
Fix bugs without affecting ongoing work
Experiment safely
Test changes before merging
Support multiple release versions

Git branches isolate work until it is ready to become part of the main code base.

Git Branch Fundamentals

A branch represents an independent line of development.

Instead of editing the main branch directly, developers create a separate branch.

Example:

			
main
 ├── feature/customer-search
 ├── feature/new-index
 ├── bugfix/order-total
 └── feature/security

		

Each branch contains:

Database objects
SQL scripts
Project files
Build configurations
Deployment scripts

Developers commit changes to their own branches until the work is complete.

Common Branch Types

Many organizations follow a branching strategy with defined branch purposes.

Main Branch

Usually called:

main
master (older repositories)

Characteristics:

Production-ready
Stable
Protected
Deployable

Direct commits are generally prohibited.

Develop Branch

Some teams maintain a separate develop branch.

Example:

			
main
↓
develop
↓
feature branches

		

Develop serves as the integration branch where completed features are merged before being promoted to production.

Feature Branches

Feature branches are created for new development work.

Examples:

			
feature/add-order-table
feature/customer-report
feature/inventory
feature/security-update

Feature branches should:

Be short-lived
Focus on one feature
Merge back quickly
Be deleted after merging

Bug Fix Branches

Bug fix branches address defects.

Examples:

			
bugfix/null-reference
bugfix/index-performance
bugfix/security

Hotfix Branches

Hotfixes repair urgent production issues.

Example:

			
main
↓
hotfix/login-timeout
↓
production

		

Hotfixes bypass normal feature development to resolve critical issues quickly.

Branching Strategies

Several branching strategies are commonly used.

GitHub Flow

Simplified workflow:

			
main
↓
feature branch
↓
Pull Request
↓
Review
↓
Merge
↓
Deploy

		

GitHub Flow is commonly used for cloud-native and continuous deployment environments.

Git Flow

Git Flow introduces additional branches:

main
develop
feature
release
hotfix

This strategy supports complex release cycles but introduces more branch management overhead.

Trunk-Based Development

In trunk-based development:

Developers create very short-lived branches.
Changes are merged frequently.
Continuous integration runs constantly.

Benefits include:

Smaller merges
Faster feedback
Reduced conflicts

Creating a Branch

Typical workflow:

			
git checkout main
git pull
git checkout -b feature/add-customer-address

The developer now works independently without affecting the main branch.

Keeping Branches Updated

Long-lived branches increase merge conflicts.

Regularly synchronize with the latest changes:

			
git fetch
git merge main

git rebase main

This keeps the feature branch aligned with current development.

Commits Within Branches

A feature branch should contain logical commits.

Good examples:

			
Add Customer table
Create Orders view
Implement RLS policy
Add clustered index
Fix foreign key constraint

		

Avoid combining unrelated changes into a single commit.

Push Changes to the Remote Repository

After committing:

git push origin feature/add-customer-address

The branch becomes available for collaboration and review.

Pull Requests (PRs)

A pull request is a request to merge one branch into another.

Example:

			
feature/add-orders
↓
Pull Request
↓
main

		

A PR provides:

Code review
Discussion
Automated validation
Build verification
Test execution
Approval workflow

Pull Request Workflow

Typical process:

			
Developer
↓
Push Branch
↓
Open Pull Request
↓
Automated Build
↓
Automated Tests
↓
Code Review
↓
Approval
↓
Merge
↓
Delete Branch

		

Benefits of Pull Requests

Pull requests improve quality by allowing reviewers to evaluate:

SQL syntax
Naming conventions
Security
Performance
Index usage
Constraints
Foreign keys
Deployment impact
Maintainability

Multiple reviewers often reduce production defects.

Branch Protection Policies

Production branches should be protected.

Typical policies include:

No direct commits
Pull request required
Minimum reviewer approval
Successful build required
Passing automated tests
Signed commits (optional)
Status checks

Protected branches reduce accidental deployments and unauthorized changes.

Automated Validation

Many organizations configure PR validation pipelines.

Before merging, the pipeline may:

Build the SQL Database Project
Generate a DACPAC
Run static code analysis
Execute unit tests
Validate SQL syntax
Verify deployment scripts

Only successful builds may be merged.

Merge Strategies

Git supports multiple merge methods.

Merge Commit

Preserves complete branch history.

			
Main ----A----B------M
          \
           C----D

Squash Merge

Combines all commits into one.

Useful when a feature contains many small commits.

History remains cleaner.

Rebase and Merge

Replays commits onto the latest branch.

Produces a linear commit history.

Some organizations prefer rebasing to simplify repository history.

Merge Conflicts

A merge conflict occurs when Git cannot determine which change should be preserved.

Example:

Developer A:

			
ALTER TABLE Customers
ADD Phone VARCHAR(20);

Developer B:

			
ALTER TABLE Customers
ADD PhoneNumber VARCHAR(25);

Git cannot determine which version is correct.

Human intervention is required.

Common Causes of Merge Conflicts

Conflicts often occur when developers modify:

The same SQL file
The same stored procedure
The same table definition
Shared configuration files
Database project files
Static data scripts

The longer branches remain separate, the greater the chance of conflicts.

Conflict Resolution Process

Typical workflow:

			
Merge Attempt
↓
Conflict Detected
↓
Review Differences
↓
Choose Correct Changes
↓
Edit File
↓
Test
↓
Commit Resolution
↓
Complete Merge

		

Best Practices for Conflict Resolution

When resolving conflicts:

Understand both developers’ changes.
Communicate with teammates if necessary.
Preserve intended functionality.
Rebuild the project.
Run automated tests.
Validate deployment scripts.
Review schema differences carefully.

Never resolve conflicts without understanding the impact.

Database-Specific Merge Challenges

Database development introduces unique considerations.

Examples include:

Simultaneous column additions
Conflicting index definitions
Different foreign key changes
Constraint modifications
Stored procedure rewrites
Permission changes
Seed data updates

Successful merges require understanding both Git operations and database semantics.

Preventing Merge Conflicts

Good practices include:

Keep branches short-lived.
Merge frequently.
Pull latest changes often.
Break large features into smaller tasks.
Communicate with team members.
Avoid editing unrelated files.
Use atomic commits.
Review changes before pushing.

Reviewing SQL Changes

Reviewers should examine:

Table modifications
New indexes
Dropped indexes
Constraints
Stored procedures
Views
Security changes
RLS policies
Permissions
Deployment scripts

Reviewing deployment impact is as important as reviewing SQL syntax.

CI/CD Integration

Branching and pull requests integrate naturally with CI/CD.

Example workflow:

			
Developer
↓
Feature Branch
↓
Commit
↓
Push
↓
Pull Request
↓
Automated Build
↓
Unit Tests
↓
Generate DACPAC
↓
Approval
↓
Merge
↓
Deployment Pipeline
↓
Development
↓
Test
↓
Production

		

Azure DevOps Integration

Azure DevOps supports:

Azure Repos
Pull requests
Branch policies
Build validation
Release pipelines
Work item linking
Reviewer assignment

Branch policies can require:

Minimum reviewers
Successful pipeline execution
Linked work items
Comment resolution

GitHub Integration

GitHub provides:

Feature branches
Pull requests
Protected branches
Required reviewers
GitHub Actions
Required status checks
Merge queues
Code Owners

GitHub Actions can automatically:

Build SQL Database Projects
Generate DACPACs
Execute validation
Publish build artifacts
Trigger deployment workflows

Best Practices

Microsoft recommends:

Never develop directly in the main branch.
Use descriptive branch names.
Keep branches short-lived.
Create focused commits.
Open pull requests early.
Require peer reviews.
Enable automated validation.
Protect production branches.
Resolve conflicts carefully.
Delete merged branches.
Continuously synchronize feature branches.
Validate deployments before merging.

DP-800 Exam Tips

Remember the following:

Feature branches isolate work.
Pull requests enable code review and automated validation.
Branch protection prevents unauthorized changes.
Merge conflicts require manual resolution.
Short-lived branches reduce conflicts.
CI pipelines should validate SQL Database Projects before merge.
SQL Database Projects work naturally with GitHub and Azure DevOps.
Automated testing should occur before deployment.
Merge strategies affect repository history but not database functionality.
Database schema changes should always originate from source-controlled projects.

Practice Exam Questions

Question 1

A development team wants to ensure that database schema changes are reviewed before being merged into the production branch. Which Git feature should they use?

A. Git tags

B. Pull requests

C. Git stash

D. Detached HEAD

Answer: B

Explanation: Pull requests provide a structured review process that allows team members to examine database changes, discuss modifications, run automated validation, and approve changes before merging.

Question 2

A developer needs to implement a new stored procedure without affecting the stability of the production branch. What is the recommended approach?

A. Commit directly to the main branch

B. Modify the production database directly

C. Create a feature branch

D. Disable branch protection

Answer: C

Explanation: Feature branches isolate development work, allowing changes to be tested and reviewed independently before being merged.

Question 3

What is the primary purpose of branch protection rules?

A. Encrypt the Git repository

B. Prevent unauthorized or unreviewed changes to important branches

C. Improve SQL query performance

D. Automatically resolve merge conflicts

Answer: B

Explanation: Branch protection policies enforce requirements such as pull requests, reviewer approvals, and successful build validation before changes are merged.

Question 4

Two developers modify the same table definition in separate branches. Git cannot determine which version should be kept during the merge. What has occurred?

A. Branch rebasing

B. Repository corruption

C. Detached HEAD

D. Merge conflict

Answer: D

Explanation: A merge conflict occurs when Git cannot automatically reconcile competing changes made to the same portion of a file.

Question 5

Which branching strategy typically uses a single production branch with short-lived feature branches and continuous integration?

A. GitHub Flow

B. Waterfall branching

C. Circular branching

D. Centralized version control

Answer: A

Explanation: GitHub Flow emphasizes a simple workflow consisting of feature branches, pull requests, continuous integration, and frequent deployment.

Question 6

What is the greatest benefit of keeping feature branches short-lived?

A. They eliminate the need for pull requests.

B. They reduce the likelihood of merge conflicts.

C. They prevent database backups.

D. They automatically optimize SQL queries.

Answer: B

Explanation: Frequent integration minimizes divergence between branches, making merges simpler and reducing the risk of conflicts.

Question 7

Which activity is commonly performed automatically when a pull request is opened?

A. Database restoration

B. Manual production deployment

C. Build validation and automated testing

D. SQL Server installation

Answer: C

Explanation: CI pipelines commonly build SQL Database Projects, validate SQL syntax, generate DACPACs, and execute automated tests before allowing a merge.

Question 8

A reviewer notices that a pull request includes unrelated changes to multiple database objects. Which best practice was violated?

A. Protected branches

B. Atomic commits and focused feature branches

C. Continuous deployment

D. Repository cloning

Answer: B

Explanation: Feature branches and commits should focus on a single logical change, making reviews easier and reducing deployment risk.

Question 9

After a pull request has been successfully merged into the main branch, what is the recommended next step for the feature branch?

A. Convert it into the production branch.

B. Rename it as “archive.”

C. Continue developing additional unrelated features.

D. Delete the merged branch.

Answer: D

Explanation: Deleting merged feature branches keeps the repository organized and encourages developers to create fresh branches for new work.

Question 10

During conflict resolution, what should a developer do before completing the merge?

A. Skip testing to save time.

B. Delete both conflicting changes.

C. Rebuild the SQL Database Project and validate the merged changes.

D. Commit the conflict markers to the repository.

Answer: C

Explanation: After resolving conflicts, the project should be rebuilt and validated to ensure the merged schema compiles successfully and behaves as expected before the merge is finalized.

Go to the DP-800 Exam Prep Hub main page

Data Development, Data Governance, Databases, DP-800, Microsoft Certification August 1, 2026

Configure source control for SQL Database Projects (DP-800 Exam Prep)

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%)
   --> Implement CI/CD by using SQL Database Projects
      --> Configure source control for SQL Database Projects

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 database development follows the same software engineering principles as application development. Rather than making changes directly in production databases, database objects are stored as source code, versioned, reviewed, tested, and deployed through automated pipelines.

SQL Database Projects provide a declarative approach to database development, where the desired database schema is maintained as source-controlled code. The database project becomes the single source of truth, and deployment tools compare the project with the target database to determine the required changes.

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

Why source control is essential
How SQL Database Projects integrate with Git
Repository structure
Branching strategies
Pull requests and code reviews
Handling schema changes
Managing deployment artifacts
Best practices for collaborative development
Integration with Azure DevOps and GitHub

Why Source Control Matters

Without source control:

Database scripts become scattered.
Multiple developers overwrite one another’s work.
Changes cannot be audited.
Rollbacks are difficult.
Production drift becomes common.

Source control provides:

Version history
Change tracking
Collaboration
Branching
Merging
Code reviews
Automated deployment
Rollback capability
Compliance auditing

Instead of the database being the authoritative copy, the SQL Database Project stored in Git becomes the authoritative definition.

SQL Database Projects Overview

A SQL Database Project stores database objects as files.

Typical objects include:

Tables
Views
Stored procedures
Functions
Triggers
Users
Roles
Schemas
Security objects
Static data scripts

When the project is built:

A DACPAC is generated.
Deployment compares the DACPAC with the target database.
Required schema changes are produced automatically.

Supported Source Control Systems

Microsoft primarily supports:

GitHub
Azure Repos (Azure DevOps)
Local Git repositories

Older systems such as Team Foundation Version Control (TFVC) are largely superseded by Git for modern development.

Typical Repository Structure

A repository commonly contains:

			
DatabaseProject/
    Database.sqlproj
    Tables/
        Customers.sql
        Orders.sql
    Views/
        vwSales.sql
    Stored Procedures/
        uspInsertOrder.sql
    Functions/
    Security/
    PostDeployment/
    PreDeployment/
    RefData/
    .gitignore
    README.md

		

Organizing objects into logical folders makes navigation and maintenance easier.

Initializing Git

After creating a SQL Database Project:

Initialize Git.
Create the repository.
Commit the initial project.
Push to GitHub or Azure DevOps.
Begin collaborative development.

Typical workflow:

			
Create Project
↓
Initialize Git
↓
Commit
↓
Push
↓
Create Branches
↓
Develop
↓
Pull Request
↓
Merge
↓
Deploy

		

Git Ignore Files

A .gitignore file prevents unnecessary files from entering source control.

Common exclusions include:

bin/
obj/
build outputs
temporary files
IDE cache files
user-specific settings

Only source code should be versioned.

What Should Be Stored in Git?

Typically stored:

SQL object definitions
SQL project file
Pre-deployment scripts
Post-deployment scripts
Static data scripts
Build configuration
Documentation
CI/CD pipeline definitions

Usually not stored:

DACPAC outputs
Temporary files
Build artifacts
IDE-generated cache
Local configuration files
Secrets
Passwords
Connection strings containing credentials

Branching Strategy

Most organizations use a branching strategy.

Example:

			
main
├── develop
│      ├── feature/customer-table
│      ├── feature/new-index
│      ├── bugfix/login
│      └── feature/security

		

Benefits include:

Parallel development
Safer releases
Easier testing
Isolated changes

Common Git Workflow

Developer workflow:

			
Pull latest code
↓
Create feature branch
↓
Modify SQL objects
↓
Build project
↓
Run tests
↓
Commit
↓
Push
↓
Open Pull Request
↓
Review
↓
Merge
↓
CI/CD deployment

		

Pull Requests

Pull requests (PRs) are central to database DevOps.

A PR allows reviewers to:

Inspect schema changes
Validate naming conventions
Review indexes
Evaluate security
Check performance
Ensure coding standards

This reduces production issues.

Merge Conflicts

Multiple developers may modify the same object.

Example:

Developer A edits:

Customers.sql

Developer B edits:

Customers.sql

Git cannot automatically determine which change is correct.

Conflict resolution requires:

Reviewing differences
Selecting the correct version
Combining changes
Testing
Rebuilding the project

Commit Best Practices

Good commit messages describe why changes were made.

Good examples:

			
Add CustomerStatus lookup table
Create uspProcessOrders procedure
Add index for OrderDate queries
Implement row-level security
Fix foreign key constraint

		

Poor examples:

			
Changes
Stuff
Update
Fix
Work

		

Atomic Commits

Each commit should represent a single logical change.

Good:

Commit 1

Add Customer table

Commit 2

Create stored procedure

Commit 3

Add index

Bad:

200 unrelated changes

Atomic commits simplify reviews and rollbacks.

Reviewing Schema Changes

Before merging:

Review:

New tables
New columns
Dropped columns
Constraint changes
Index additions
Foreign keys
Permissions
Views
Stored procedures

Ensure no unintended schema changes exist.

Database Drift

Database drift occurs when production changes bypass source control.

Example:

Developer directly executes:

			
ALTER TABLE Customers
ADD PhoneNumber VARCHAR(20)

The SQL Database Project remains unchanged.

Consequences:

Future deployments may remove the column.
Schema becomes inconsistent.
Production no longer matches source control.

Best practice:

All changes originate in the SQL Database Project.

Working with Azure DevOps

Azure DevOps integrates SQL Database Projects with:

Azure Repos
Azure Pipelines
Pull Requests
Branch Policies
Work Items
Release Pipelines

Typical flow:

			
Developer
↓
Azure Repos
↓
Pull Request
↓
Build Pipeline
↓
Validation
↓
Merge
↓
Release Pipeline
↓
Azure SQL Database

		

Working with GitHub

GitHub supports:

Git repositories
Pull requests
Protected branches
GitHub Actions
Issue tracking
Code review
Automated deployments

GitHub Actions can automatically:

Build SQL Database Projects
Generate DACPAC files
Execute validation
Deploy to staging
Deploy to production after approval

Branch Protection

Production branches should be protected.

Common policies:

Pull request required
Minimum reviewers
Successful build required
No direct commits
Signed commits (optional)
Required status checks

This greatly improves deployment quality.

Handling Secrets

Never commit:

SQL passwords
Azure keys
API keys
Tokens
Certificates
Connection strings with credentials

Instead use:

Azure Key Vault
GitHub Secrets
Azure DevOps Variable Groups
Managed Identity

CI/CD Integration

Source control enables continuous integration.

Typical process:

			
Commit
↓
Build
↓
Validate SQL
↓
Run Tests
↓
Create DACPAC
↓
Publish Artifact
↓
Deploy Dev
↓
Deploy Test
↓
Deploy Production

		

Source Control Best Practices

Microsoft recommends:

Keep database definitions in Git.
Use feature branches.
Require pull requests.
Keep commits small.
Review schema changes.
Build every commit.
Automate deployments.
Protect production branches.
Never commit secrets.
Use SQL Database Projects as the source of truth.
Avoid direct production changes.
Keep repository structure organized.

DP-800 Exam Tips

Remember these key points:

SQL Database Projects integrate naturally with Git.
GitHub and Azure DevOps are the primary source control platforms.
Use feature branches instead of committing directly to main.
Protect production branches with policies.
Pull requests enable peer review.
Source control prevents database drift.
Build validation should occur before merging.
Store database definitions—not deployed databases—in source control.
Secrets belong in secure secret stores, not Git repositories.
CI/CD pipelines should deploy from source-controlled SQL Database Projects.

Practice Exam Questions

Question 1

A development team wants every database schema change to be reviewed before deployment. Which Git feature best supports this requirement?

A. Git tags

B. Pull requests

C. Local branches

D. Git stash

Answer: B

Explanation:
Pull requests enable peer review, discussion, automated validation, and approval before changes are merged into protected branches.

Question 2

Which item should generally NOT be committed to a SQL Database Project repository?

A. Stored procedures

B. Table definitions

C. Build output DACPAC files

D. Database project file

Answer: C

Explanation:
Build artifacts such as generated DACPAC files can be recreated and are typically excluded via .gitignore.

Question 3

A developer needs to implement a new reporting view without affecting ongoing work by teammates. What is the recommended approach?

A. Commit directly to the main branch

B. Modify the production database first

C. Create a feature branch

D. Disable branch protection

Answer: C

Explanation:
Feature branches isolate development work, allowing changes to be tested and reviewed before merging.

Question 4

What is the primary purpose of branch protection rules?

A. Improve query execution speed

B. Encrypt repository contents

C. Automatically resolve merge conflicts

D. Prevent unauthorized or unreviewed changes to critical branches

Answer: D

Explanation:
Branch protection enforces policies such as required reviews and successful builds before changes can be merged.

Question 5

A production database contains schema changes that were made directly using SQL Server Management Studio instead of through the SQL Database Project. This situation is known as:

A. Database drift

B. Schema normalization

C. Dependency injection

D. Continuous deployment

Answer: A

Explanation:
Database drift occurs when deployed databases differ from the schema defined in source control.

Question 6

Why should commits generally be small and focused?

A. They eliminate the need for testing.

B. They increase deployment speed automatically.

C. They simplify reviews, troubleshooting, and rollbacks.

D. They prevent merge conflicts entirely.

Answer: C

Explanation:
Atomic commits make it easier to understand changes, review code, identify issues, and revert individual modifications if necessary.

Question 7

Where should sensitive connection strings and passwords typically be stored?

A. README.md

B. SQL project file

C. Source-controlled configuration file

D. Azure Key Vault or another secure secret store

Answer: D

Explanation:
Secrets should never be committed to source control. Secure secret management services protect sensitive credentials.

Question 8

Which activity is commonly performed automatically by a CI pipeline after code is committed?

A. Manual code review

B. Physical database backup

C. Building the SQL Database Project and validating it

D. Creating user accounts

Answer: C

Explanation:
Continuous Integration pipelines commonly build the project, validate the schema, execute automated tests, and produce deployment artifacts.

Question 9

What is the primary benefit of using pull requests for SQL Database Projects?

A. They provide structured code review before merging changes.

B. They replace source control.

C. They eliminate the need for deployment pipelines.

D. They permanently lock database objects.

Answer: A

Explanation:
Pull requests facilitate collaboration, improve code quality, and ensure that schema changes are reviewed before becoming part of the main codebase.

Question 10

Which statement best describes the role of a SQL Database Project in a DevOps workflow?

A. It stores only database backups.

B. It replaces Git repositories.

C. It serves as the authoritative, source-controlled definition of the database schema.

D. It is used only during production deployment.

Answer: C

Explanation:
In modern database DevOps, the SQL Database Project is the single source of truth for the database schema. Deployment tools compare this project with target databases to generate the required schema changes automatically.

Go to the DP-800 Exam Prep Hub main page

Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 2 (DP-800 Exam Prep)

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%)
   --> Implement CI/CD by using SQL Database Projects
      --> Create, build, and validate database models by using SQL Database Projects, including SDK-style models

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

In Part 1, you learned about SQL Database Projects, database models, SDK-style projects, build validation, and DACPAC generation. In this section, we’ll examine how developers work with existing databases, manage dependencies, validate projects, deploy changes, and implement modern DevOps practices.

Importing an Existing Database into a SQL Database Project

Many organizations already have production databases before adopting Database-as-Code practices. Rather than starting from scratch, developers can import an existing schema into a SQL Database Project.

The import process typically:

Connects to an existing SQL Server or Azure SQL Database.
Reads the database schema.
Extracts supported objects.
Creates corresponding .sql files.
Generates the SQL Database Project.

Objects that are typically imported include:

Tables
Views
Stored procedures
Functions
User-defined data types
Schemas
Security objects
Synonyms
Sequences

Data itself is not imported into the project.

Reverse Engineering a Database

Importing is often called reverse engineering because the project is generated from an existing database rather than the database being created from source code.

Example workflow:

			
Production Database
        │
        ▼
Extract Schema
        │
        ▼
Generate SQL Project
        │
        ▼
Commit to Git
        │
        ▼
Future Changes Through Source Control

		

This allows teams to transition from manual database administration to modern DevOps practices.

Source Control Integration

One of the biggest advantages of SQL Database Projects is seamless integration with Git.

A repository may contain:

			
DatabaseProject/
│
├── Tables/
├── Views/
├── Procedures/
├── Security/
├── Scripts/
├── Database.sqlproj
└── README.md

		

Each change becomes a Git commit, providing:

Version history
Code reviews
Branching
Pull requests
Rollback capabilities
Team collaboration

Branching Strategies

Common Git workflows include:

Feature Branches

Each developer works in an isolated branch.

			
Main
├── Feature-A
├── Feature-B
└── Feature-C

Changes are merged only after review and successful validation.

Release Branches

Organizations often create release branches for production deployments.

Example:

			
Main
↓
Release 1.0
↓
Production

		

This ensures stable production releases.

Database References

Large enterprise systems often contain multiple databases.

Examples include:

Sales
Inventory
Human Resources
Finance

Applications frequently reference objects across databases.

SQL Database Projects support database references to resolve these dependencies during the build process.

Example of a Cross-Database Reference

Suppose a stored procedure references another database:

			
SELECT *
FROM Inventory.dbo.Products;

Without a database reference, the build reports an unresolved reference.

Adding a database reference informs the build engine where the referenced objects reside.

Project References

A SQL Database Project can reference another SQL Database Project.

Example:

			
SalesDatabase
↓
References
↓
SharedDatabase

		

This allows developers to:

Reuse shared schemas
Validate dependencies
Build multiple databases together

Schema Compare

Schema Compare is one of the most valuable tools in SQL Database Projects.

It compares:

Project vs Database
Database vs Database
Project vs DACPAC
DACPAC vs Database

The comparison identifies differences before deployment.

Schema Compare Example

Suppose the project contains:

CustomerName NVARCHAR(200)

Production contains:

CustomerName NVARCHAR(100)

Schema Compare highlights the difference before deployment.

Why Schema Compare Matters

Schema Compare helps prevent:

Missing objects
Accidental deletions
Unexpected schema drift
Incorrect deployments
Manual mistakes

It also generates deployment scripts automatically.

Schema Drift

Schema drift occurs when changes are made directly to a production database instead of through the SQL Database Project.

Example:

Project:

			
Employee
Salary

Production:

			
Employee
Salary
Bonus

The project is now out of sync.

Schema Compare identifies this difference.

Build Process

Building a SQL Database Project performs several validation steps:

Parse SQL files
Validate syntax
Resolve dependencies
Build the database model
Detect conflicts
Generate the DACPAC

Only after these steps succeed is the project considered buildable.

Common Build Errors

Examples include:

Missing Table

			
SELECT *
FROM Orders;

If the Orders table does not exist, the build fails.

Invalid Column

			
SELECT CustomerAge
FROM Customers;

If CustomerAge is absent, validation reports an error.

Duplicate Object

Two files define:

CREATE TABLE Customers

The project cannot determine which definition is correct, so the build fails.

Circular Dependency

View A depends on View B.

View B depends on View A.

This circular dependency prevents successful validation.

Build Warnings vs Build Errors

Warning	Error
Build succeeds	Build fails
Potential issue	Must be fixed
Deployment possible	Deployment blocked
Review recommended	Immediate action required

Developers should investigate warnings even if the build succeeds.

Pre-Deployment Scripts

Pre-deployment scripts execute before schema deployment.

Typical uses include:

Backups
Temporary objects
Data preparation
Environment validation
Configuration checks

Example:

PRINT 'Preparing deployment';

Post-Deployment Scripts

Post-deployment scripts execute after schema deployment.

Typical tasks include:

Insert lookup data
Populate configuration tables
Create default users
Update permissions
Seed application settings

Example:

			
INSERT INTO Status
VALUES ('Active');

SQLPackage

SQLPackage is Microsoft’s command-line utility for SQL Database Projects.

It can:

Build projects
Publish DACPACs
Extract schemas
Generate deployment scripts
Compare schemas
Export DACPACs

SQLPackage is widely used in automated deployment pipelines.

Common SQLPackage Operations

Developers commonly use SQLPackage to:

Publish a DACPAC to Azure SQL Database.
Extract a DACPAC from an existing database.
Generate deployment scripts without applying them.
Compare source and target schemas.

This enables repeatable, automated deployments.

Continuous Integration (CI)

A CI pipeline typically performs:

			
Git Commit
↓
Restore
↓
Build SQL Project
↓
Validate Model
↓
Run Tests
↓
Generate DACPAC
↓
Publish Build Artifact

		

Every commit is validated automatically.

Continuous Delivery (CD)

The CD pipeline deploys validated artifacts.

Typical workflow:

			
DACPAC
↓
Development
↓
Testing
↓
Staging
↓
Production

		

Promotion between environments follows organizational approval policies.

Deployment Validation

Before deployment, the deployment engine evaluates:

Schema differences
Data loss risks
Object dependencies
Permission changes
Unsupported operations

Potentially destructive changes, such as dropping a populated table, are flagged for review.

Environment-Specific Configuration

Projects should avoid hard-coding environment-specific settings.

Instead, deployment profiles or pipeline variables should define values such as:

Server name
Database name
Authentication method
Connection strings
Environment-specific options

This supports consistent deployments across development, test, and production.

SDK-Style Project Best Practices

Microsoft recommends the following practices:

Store every schema object in its own file.
Use meaningful folder structures.
Commit all schema changes to source control.
Build frequently.
Resolve warnings before deployment.
Validate pull requests automatically.
Use deployment profiles for different environments.
Automate builds with CI/CD pipelines.
Minimize manual production changes.
Keep database references current.

Common DP-800 Exam Scenarios

Scenario 1

A developer changes a table directly in production.

Question: What problem has occurred?

Answer: Schema drift.

Scenario 2

A project builds successfully but deployment has not occurred.

Question: What artifact was created?

Answer: A DACPAC.

Scenario 3

A stored procedure references another database and validation fails.

Question: What should be added?

Answer: A database reference (or project reference, where appropriate).

Scenario 4

A team wants every schema change reviewed before deployment.

Recommended approach:

Git repository
Pull requests
SQL Database Projects
Automated build validation
DACPAC deployment

DP-800 Exam Tips

Understand the difference between project references and database references.
Know how Schema Compare identifies schema drift and deployment differences.
Recognize when to use pre-deployment versus post-deployment scripts.
Be familiar with SQLPackage as the primary command-line deployment tool.
Understand that CI pipelines build, validate, and generate DACPACs, while CD pipelines deploy those validated artifacts.
Remember that schema validation occurs before deployment, helping detect unresolved references, duplicate objects, and dependency issues.

Key Takeaways

Existing databases can be reverse engineered into SQL Database Projects.
Source control enables collaboration, auditing, and rollback.
Database and project references resolve dependencies across databases.
Schema Compare identifies schema differences and drift.
SQLPackage automates building, extracting, comparing, and deploying database projects.
CI/CD pipelines automate validation and deployment.
Pre-deployment and post-deployment scripts help manage operational tasks during deployment.
SDK-style projects reduce maintenance while supporting modern DevOps workflows.

Practice Exam Questions

Question 1

A development team wants to ensure that all database schema changes are version controlled, reviewed through pull requests, and automatically validated before deployment.

Which approach should they implement?

A. Store the database schema in a SQL Database Project managed in Git and use CI/CD pipelines.

B. Allow developers to make schema changes directly in production and back up the database daily.

C. Export a database backup after every schema change.

D. Maintain documentation of schema changes in a shared spreadsheet.

Correct Answer: A

Explanation

SQL Database Projects support Database-as-Code practices by storing database objects in source control. Combined with Git and CI/CD pipelines, schema changes can be reviewed, validated, tested, and deployed consistently. The other options lack automation, version control, and build validation.

Question 2

What is the primary output generated when a SQL Database Project is successfully built?

A. A transaction log

B. A DACPAC

C. A backup (.bak) file

D. A SQL trace file

Correct Answer: B

Explanation

A successful build generates a DACPAC (Data-tier Application Package) that contains the compiled database model. It serves as the deployment artifact for publishing schema changes. A backup file and transaction log contain database data, not compiled schema definitions.

Question 3

A stored procedure references a table in another database. During the build process, an unresolved reference error occurs.

What should you configure?

A. A post-deployment script

B. A schema comparison

C. A database reference

D. Query Store

Correct Answer: C

Explanation

Database references inform the build engine about objects located in external databases, allowing dependency validation during compilation. Without the reference, the build engine cannot resolve cross-database object names.

Question 4

Which statement accurately describes an SDK-style SQL Database Project?

A. It requires every SQL file to be manually added to the project file.

B. It supports only Azure SQL Database.

C. It cannot be used with Git.

D. It automatically discovers SQL files and uses a simplified project format.

Correct Answer: D

Explanation

SDK-style projects simplify project configuration by automatically discovering SQL files and using a modern SDK-based project structure. This reduces maintenance, improves Git compatibility, and supports cross-platform development.

Question 5

During a build, a view references a table that no longer exists.

What is the expected outcome?

A. The build reports a validation error.

B. The DACPAC is generated without warnings.

C. The table is automatically recreated.

D. The deployment succeeds and fixes the dependency.

Correct Answer: A

Explanation

The build engine validates object dependencies while constructing the database model. Missing referenced objects generate validation errors that prevent a successful build until the dependency is resolved.

Question 6

Your team notices that a production database contains several tables that are not present in the SQL Database Project because administrators modified production directly.

What situation does this describe?

A. Database normalization

B. Incremental deployment

C. Schema drift

D. Model optimization

Correct Answer: C

Explanation

Schema drift occurs whenever changes are made outside the controlled development process, causing production and source control to diverge. Schema Compare is commonly used to detect these differences.

Question 7

Which tool is specifically designed to compare differences between a SQL Database Project and a target database before deployment?

A. Query Store

B. SQL Profiler

C. SQL Server Agent

D. Schema Compare

Correct Answer: D

Explanation

Schema Compare analyzes differences between schemas stored in projects, DACPACs, and databases. It helps identify schema drift and generates deployment scripts before changes are applied.

Question 8

Why is compile-time validation an important feature of SQL Database Projects?

A. It encrypts the deployed database automatically.

B. It detects schema and dependency problems before deployment.

C. It improves query execution speed.

D. It compresses database backups.

Correct Answer: B

Explanation

Compile-time validation identifies syntax errors, unresolved references, duplicate objects, and dependency problems before deployment, reducing production failures and improving deployment reliability.

Question 9

Which activity is most appropriate for a post-deployment script?

A. Building the DACPAC

B. Validating SQL syntax

C. Inserting lookup or reference data after schema deployment

D. Resolving project references

Correct Answer: C

Explanation

Post-deployment scripts execute after schema changes have been applied. Common tasks include inserting lookup data, populating configuration tables, creating default records, and updating permissions.

Question 10

Which statement best describes the relationship between Continuous Integration (CI) and SQL Database Projects?

A. CI replaces the need for SQL Database Projects.

B. CI automatically converts databases into NoSQL databases.

C. CI performs backups before every deployment.

D. CI automatically builds, validates, and produces deployment artifacts whenever changes are committed.

Correct Answer: D

Explanation

Continuous Integration automates the process of building SQL Database Projects, validating database models, detecting errors, and generating DACPAC deployment artifacts whenever developers commit changes. This enables early detection of issues and supports reliable, repeatable deployments.

Exam Tips

Know the difference between a SQL Database Project, a database model, and a DACPAC.
Remember that SDK-style projects automatically discover SQL files and simplify project maintenance.
Understand the purpose of database references and project references.
Be able to identify scenarios involving schema drift and understand how Schema Compare addresses them.
Know the difference between pre-deployment and post-deployment scripts.
Understand how SQLPackage, CI/CD pipelines, and Git work together to automate database deployments.
Expect scenario-based questions that ask you to choose the appropriate development or deployment strategy for a given situation.

Go to the DP-800 Exam Prep Hub main page

Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Create, build, and validate database models by using SQL Database Projects, including SDK-style models – Part 1 (DP-800 Exam Prep)

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%)
   --> Implement CI/CD by using SQL Database Projects
      --> Create, build, and validate database models by using SQL Database Projects, including SDK-style models

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

For the exam, you should understand how to:

Create SQL Database Projects
Build database models
Validate database schemas before deployment
Use SDK-style SQL projects
Work with DACPACs
Manage project references
Integrate SQL Database Projects into DevOps pipelines
Detect schema problems before deployment
Support collaborative database development

This topic is one of the most important DevOps-related objectives on the DP-800 exam because Microsoft encourages Database-as-Code (DbC) practices.

What Is a SQL Database Project?

A SQL Database Project is a source-controlled representation of a SQL Server or Azure SQL database.

Instead of editing objects directly inside the database, developers edit project files that describe every object.

The project can then be:

Built
Validated
Version controlled
Tested
Published

Think of it as treating a database exactly like application code.

Instead of storing the database only on a SQL Server instance, the schema becomes part of the application’s source code repository.

Traditional Database Development vs SQL Database Projects

Traditional Development	SQL Database Projects
Direct changes in SSMS	Changes made in project files
Difficult to track history	Full Git history
Manual deployments	Automated deployments
Hard to validate	Build-time validation
Production-first changes	Development-first workflow
Error detection during deployment	Error detection during build

Database-as-Code (DbC)

SQL Database Projects implement the Database-as-Code methodology.

Database objects become code files that can be:

reviewed
versioned
tested
validated
automatically deployed

Just like C# or Java projects.

Benefits include:

Consistent deployments
Easier collaboration
Rollback capability
Repeatable deployments
Reduced production errors
CI/CD integration

Components of a SQL Database Project

A project typically contains:

			
DatabaseProject
│
├── Tables
│   ├── Customers.sql
│   ├── Orders.sql
│
├── Views
│   ├── SalesSummary.sql
│
├── Stored Procedures
│   ├── usp_InsertOrder.sql
│
├── Functions
│
├── Security
│
├── Users
│
├── Roles
│
├── Schemas
│
├── Scripts
│
├── PostDeployment.sql
│
├── PreDeployment.sql
│
└── Database.sqlproj

		

Every object is stored as an individual SQL file.

What Is a Database Model?

A database model is the complete representation of every database object contained within a SQL Database Project.

It includes:

Tables
Columns
Primary keys
Foreign keys
Constraints
Views
Stored procedures
Functions
Triggers
Users
Roles
Schemas
Permissions

The model exists independently of any live database.

Microsoft builds this model during compilation.

Why Build a Database Model?

Building the model allows SQL Server Data Tools (SSDT) or SQL Database Projects to verify:

Object existence
Dependency correctness
Syntax correctness
Invalid references
Circular dependencies
Duplicate objects
Naming conflicts

before deployment.

SQL Database Projects vs DACPAC

These two concepts are closely related but not identical.

SQL Database Project

Contains:

Source files
SQL scripts
Project configuration
Build settings

Editable by developers.

DACPAC

A Data-tier Application Package (DACPAC) is the compiled output generated from the project.

Think of it like:

			
C# Source Code
↓
DLL

Similarly,

			
SQL Project
↓
DACPAC

The DACPAC contains:

Database model
Schema metadata
Deployment information

It does not contain user data.

Development Workflow

A typical workflow looks like this:

			
Developer
↓
Modify SQL files
↓
Build project
↓
Validate model
↓
Generate DACPAC
↓
Source Control
↓
CI Pipeline
↓
Testing
↓
Deployment
↓
Production

		

This workflow ensures every schema change is validated before deployment.

Creating a SQL Database Project

Common methods include:

Visual Studio
Azure Data Studio (with SQL Database Projects extension)
Visual Studio Code (SQL Database Projects extension)
.NET CLI (SDK-style projects)

Typical steps:

Create project
Choose SQL Server platform
Add database objects
Build project
Resolve validation errors
Generate DACPAC
Deploy

SQL Server Data Tools (SSDT)

Historically, SSDT was the primary development environment.

It provides:

IntelliSense
Schema Compare
Build validation
Refactoring
Deployment
Publish wizard

Modern SQL Database Projects also support lightweight editors like Visual Studio Code.

SDK-Style SQL Database Projects

The newer SDK-style format modernizes SQL project development.

Benefits include:

Simpler project files
Cross-platform support
.NET SDK integration
Better Git compatibility
Easier automation
Better Azure DevOps integration
Improved command-line support

Microsoft is increasingly encouraging SDK-style projects over older project formats.

Traditional Project Format

Older projects contain verbose XML.

Example:

			
<Project DefaultTargets="Build">
<ItemGroup>
<Build Include="Tables\Customer.sql"/>
<Build Include="Views\Sales.sql"/>
</ItemGroup>
</Project>

		

As projects grow, these files become difficult to maintain.

SDK-Style Project Format

SDK-style projects are dramatically simpler.

Example:

			
<Project Sdk="Microsoft.Build.Sql">
<PropertyGroup>
<TargetFramework>net8.0</TargetFramework>
<SqlServerVersion>Sql160</SqlServerVersion>
</PropertyGroup>
</Project>

		

Files are automatically discovered.

Developers no longer have to manually list every SQL object.

Advantages of SDK-Style Projects

Compared to legacy projects:

Traditional	SDK-Style
Large XML	Minimal XML
Manual file inclusion	Automatic discovery
Windows-focused	Cross-platform
Older MSBuild	Modern SDK
More maintenance	Less maintenance
Limited CLI support	Excellent CLI support

Automatic File Discovery

One major benefit is automatic inclusion.

Suppose a developer creates:

			
Tables
Products.sql

The project automatically includes it.

No project modification is required.

This greatly reduces merge conflicts in Git.

Platform Targets

Projects target a SQL platform.

Examples include:

SQL Server 2019
SQL Server 2022
Azure SQL Database
Azure SQL Managed Instance

The selected platform determines which SQL features are valid.

For example:

A feature available in SQL Server 2022 but not Azure SQL Database may produce a build warning or error if the wrong target platform is selected.

Schema Validation

During the build, SQL Database Projects perform extensive validation.

Checks include:

Missing tables
Missing columns
Invalid views
Invalid stored procedures
Invalid foreign keys
Duplicate objects
Broken references
Unsupported features
Syntax errors

This allows developers to catch issues long before deployment.

Dependency Analysis

The build engine understands dependencies.

For example:

			
View
↓
Table
↓
Schema

		

If a table is renamed without updating dependent objects, the build detects the issue.

Object Dependency Example

Consider:

			
CREATE VIEW SalesSummary
AS
SELECT *
FROM Sales;

If the Sales table is removed, the build process reports an error because the view references a nonexistent object.

Compile-Time Validation vs Runtime Validation

Compile-Time	Runtime
During build	During execution
Finds schema errors early	Errors appear after deployment
Faster troubleshooting	Production outages possible
Safer deployments	Higher operational risk

Compile-time validation is one of the biggest advantages of SQL Database Projects.

Common DP-800 Exam Tips

Understand the distinction between a SQL Database Project and a DACPAC.
Know that SQL Database Projects implement Database-as-Code practices.
Recognize that SDK-style projects simplify project maintenance through automatic file discovery and modern MSBuild integration.
Remember that the database model is built and validated before deployment, helping identify schema issues early.
Be familiar with how build validation detects missing objects, dependency problems, and syntax errors before changes reach production.
Know that SQL Database Projects integrate naturally with Git, Azure DevOps, and GitHub workflows for CI/CD.

Key Takeaways

SQL Database Projects represent database schemas as source code.
Database models are compiled representations of all database objects.
Building a project validates the model before deployment.
DACPACs are compiled deployment artifacts generated from SQL Database Projects.
SDK-style projects simplify configuration, support cross-platform development, and improve automation.
Automatic file discovery reduces project maintenance and Git merge conflicts.
Compile-time validation helps prevent deployment failures by identifying schema and dependency issues early.

Go to the DP-800 Exam Prep Hub main page

Data Development, DP-800, Microsoft Certification, SQL August 1, 2026

Identify and resolve query performance issues, including blocking and deadlocks – Part 1 (DP-800 Exam Prep)

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%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks

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

Efficient query performance is one of the most important responsibilities of a SQL developer. Regardless of whether a database is hosted in SQL Server, Azure SQL Database, Azure SQL Managed Instance, or Microsoft Fabric SQL Database, applications depend on queries executing quickly while maintaining data consistency and supporting concurrent users.

Poor-performing queries can cause excessive CPU usage, memory pressure, storage bottlenecks, long response times, and application outages. Likewise, poorly managed concurrency can result in blocking and deadlocks that significantly impact user productivity.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how SQL Server manages concurrent transactions, recognize common performance issues, detect blocking and deadlocks, and apply best practices to resolve these problems.

Learning Objectives

After completing this article, you should be able to:

Explain why query performance optimization is important.
Identify common causes of poor query performance.
Understand SQL Server locking behavior.
Explain blocking and deadlocks.
Recognize how transaction isolation levels affect concurrency.
Detect blocking sessions.
Detect deadlocks.
Apply techniques to reduce blocking and deadlocks.
Troubleshoot real-world concurrency problems.

Why Query Performance Matters

Every SQL query consumes system resources. Poorly optimized queries consume more resources than necessary and may affect every user connected to the database.

Common consequences include:

Slow application response times
High CPU utilization
Excessive memory consumption
Increased disk I/O
Long-running transactions
Lock contention
Blocking
Deadlocks
Reduced scalability

Database performance is not solely about executing a single query quickly—it is about enabling thousands of users to work simultaneously without interfering with each other.

Common Causes of Poor Query Performance

Many performance problems originate from inefficient query design.

Common causes include:

Missing Indexes

Without appropriate indexes, SQL Server performs table scans rather than index seeks.

Instead of reading a few rows:

CustomerID = 1205

SQL Server may need to scan millions of rows.

Symptoms include:

High logical reads
High physical reads
Increased CPU usage
Long execution times

Poor Index Design

Too many indexes can slow writes.

Too few indexes slow reads.

Poor index design includes:

Incorrect clustered indexes
Missing covering indexes
Duplicate indexes
Unused indexes
Highly fragmented indexes

Returning More Data Than Necessary

Instead of:

			
SELECT *
FROM Sales.Orders;

Use:

			
SELECT OrderID,
       CustomerID,
       OrderDate
FROM Sales.Orders;

Benefits include:

Reduced network traffic
Less memory usage
Faster execution
Smaller execution plans

Non-SARGable Queries

SARGable means Search Argument Able.

Bad example:

WHERE YEAR(OrderDate) = 2025

Because SQL Server must calculate YEAR() for every row.

Better:

			
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Now an index on OrderDate can be used.

Implicit Data Type Conversions

Example:

WHERE CustomerID = '100'

if CustomerID is an integer.

SQL Server may convert every value before comparison.

Better:

WHERE CustomerID = 100

Outdated Statistics

Statistics help the optimizer estimate row counts.

Outdated statistics lead to:

Poor cardinality estimates
Incorrect join choices
Bad execution plans
Longer execution times

Parameter Sniffing

Stored procedures reuse cached execution plans.

A plan optimized for:

CustomerID = 1

may perform poorly for:

CustomerID = 999999

DP-800 candidates should understand that parameter sniffing can sometimes degrade performance and that techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, or query hints may be used selectively to address it.

Understanding Locking

SQL Server uses locks to ensure:

Data consistency
Transaction isolation
Integrity during concurrent access

Locks prevent conflicting operations from occurring simultaneously.

Example:

User A updates:

OrderID = 100

Before User A commits,

User B attempts to update the same row.

SQL Server places User B into a waiting state until User A completes.

This waiting is called blocking.

Types of Locks

Several lock types are important for the DP-800 exam.

Shared (S)

Used for reading.

Multiple users may hold Shared locks simultaneously.

Example:

SELECT

Exclusive (X)

Used for modifications.

Example:

			
UPDATE
DELETE
INSERT

Only one Exclusive lock can exist on a resource.

Update (U)

Used during updates.

Prevents certain deadlock scenarios.

Typically upgraded to an Exclusive lock when data is modified.

Intent Locks

Used internally.

Examples include:

These indicate SQL Server intends to place locks at lower levels.

Schema Locks

Protect database object definitions.

Examples:

ALTER TABLE

CREATE INDEX

Lock Granularity

SQL Server can lock at multiple levels.

Row
Key
Page
Extent
Table
Database

Smaller locks improve concurrency.

Larger locks reduce overhead but may increase blocking.

Lock Escalation

SQL Server may automatically replace many row locks with a table lock.

Example:

Instead of:

20,000 row locks

SQL Server escalates to:

One table lock

Benefits:

Lower memory usage

Drawback:

More blocking

Understanding Blocking

Blocking occurs when one session waits for another session to release a lock.

Example

Session 1:

			
BEGIN TRANSACTION;
UPDATE Products
SET Price = Price * 1.05
WHERE ProductID = 5;

Transaction remains open.

Session 2:

			
SELECT *
FROM Products
WHERE ProductID = 5;

Session 2 waits.

This is normal behavior.

Blocking protects data consistency.

When Blocking Becomes a Problem

Short blocking is expected.

Long blocking causes:

Slow applications
Timeouts
User frustration
Connection pooling issues
Increased resource usage

Common causes include:

Long-running transactions
User interaction inside transactions
Large batch updates
Missing indexes
Table scans
Poor query design

Understanding Deadlocks

A deadlock occurs when two or more sessions permanently wait for each other.

Example

Session A

Locks:

Customers

Needs:

Orders

Session B

Locks:

Orders

Needs:

Customers

Neither session can continue.

SQL Server automatically detects the deadlock.

One transaction becomes the deadlock victim.

Its transaction is rolled back.

The other transaction continues.

Deadlock Example

Transaction A

			
BEGIN TRANSACTION;
UPDATE Customers
SET CreditLimit = 1000
WHERE CustomerID = 1;
UPDATE Orders
SET Status = 'Approved'
WHERE OrderID = 100;
COMMIT;

		

Transaction B

			
BEGIN TRANSACTION;
UPDATE Orders
SET Status = 'Pending'
WHERE OrderID = 100;
UPDATE Customers
SET CreditLimit = 900
WHERE CustomerID = 1;
COMMIT;

		

If both transactions execute simultaneously:

Transaction A locks Customers
Transaction B locks Orders
Each waits for the other’s lock

SQL Server detects the cycle and terminates one transaction.

Blocking vs. Deadlocks

Blocking	Deadlock
Temporary waiting	Circular waiting
Usually resolves automatically	Requires SQL Server intervention
No transaction rollback	One transaction rolled back
Normal behavior	Undesirable behavior
Caused by incompatible locks	Caused by cyclic lock dependencies

Transaction Isolation Levels

Isolation levels determine how transactions interact.

They directly affect:

Blocking
Concurrency
Consistency
Performance

READ UNCOMMITTED

Lowest isolation.

Allows dirty reads.

Almost no blocking.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages

Very fast

Disadvantages

Reads uncommitted data

READ COMMITTED (Default)

Most common.

Prevents dirty reads.

Allows non-repeatable reads.

Balanced performance and consistency.

REPEATABLE READ

Protects rows already read.

Increases locking.

More blocking.

SERIALIZABLE

Highest isolation.

Maximum consistency.

Most locking.

Greatest blocking potential.

SNAPSHOT Isolation

Uses row versioning.

Readers do not block writers.

Writers do not block readers.

Advantages:

High concurrency
Fewer blocking issues
Better scalability

Requires enabling snapshot isolation in the database.

Choosing the Appropriate Isolation Level

Isolation Level	Dirty Reads	Blocking	Concurrency
READ UNCOMMITTED	Yes	Very Low	Very High
READ COMMITTED	No	Moderate	Good
REPEATABLE READ	No	Higher	Moderate
SERIALIZABLE	No	Highest	Lowest
SNAPSHOT	No	Low	Excellent

Detecting Blocking

Several tools can identify blocking.

Common methods include:

SQL Server Management Studio Activity Monitor
Dynamic Management Views (DMVs)
Extended Events
SQL Server Profiler (legacy)
Azure SQL monitoring tools
Microsoft Fabric monitoring experiences

One useful DMV query is:

			
SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

		

This displays:

Waiting session
Blocking session
Wait type
Wait duration
Locked resource

Detecting Deadlocks

SQL Server automatically detects deadlocks.

Detection methods include:

Extended Events
System Health session
SQL Server Profiler (legacy)
Azure SQL Intelligent Insights
Deadlock graphs
SQL Server error logs (when configured)

Deadlock graphs visually display:

Victim process
Lock owners
Waiting processes
Resources involved

These graphs are invaluable for identifying the exact sequence of events that caused the deadlock.

Best Practices to Prevent Blocking and Deadlocks

Microsoft recommends several strategies to minimize concurrency issues:

Keep transactions as short as possible.
Commit or roll back transactions promptly.
Access tables in a consistent order across all applications.
Create appropriate indexes to reduce scan times.
Avoid user interaction while a transaction is open.
Use the lowest appropriate isolation level for the workload.
Consider Snapshot Isolation or Read Committed Snapshot Isolation (RCSI) for read-heavy environments.
Break large updates into smaller batches.
Regularly maintain indexes and statistics.
Monitor blocking trends and deadlock frequency proactively.

Real-World Troubleshooting Scenarios

Scenario 1: Long-Running Transaction

A reporting application begins a transaction and leaves it open while waiting for user input. Meanwhile, hundreds of users attempting to update the same data experience delays.

Resolution: Redesign the application so that user interaction occurs before the transaction begins or after it commits, minimizing the transaction’s duration.

Scenario 2: Deadlocks During Order Processing

Two stored procedures update the Customers and Orders tables but access them in different sequences.

Resolution: Standardize the order in which tables are accessed (for example, always update Customers before Orders) to eliminate the circular dependency that causes deadlocks.

Scenario 3: Blocking Caused by Table Scans

A frequently executed query scans millions of rows because no suitable index exists. The scan holds locks long enough to block other sessions.

Resolution: Create an appropriate nonclustered index and rewrite the query to be SARGable so that SQL Server can perform index seeks instead of table scans.

DP-800 Exam Tips

Understand the difference between blocking and deadlocks.
Know how transaction isolation levels affect concurrency and locking behavior.
Recognize that blocking is a normal mechanism to preserve consistency, whereas deadlocks are abnormal conditions that SQL Server resolves by selecting a victim transaction.
Be familiar with common lock types, including Shared, Exclusive, Update, Intent, and Schema locks.
Know that Snapshot Isolation and Read Committed Snapshot Isolation (RCSI) use row versioning to reduce reader-writer blocking.
Understand that long-running transactions, missing indexes, inconsistent object access order, and poor query design are common causes of blocking and deadlocks.
Be comfortable using DMVs and monitoring tools to identify blocking sessions before moving on to advanced analysis with execution plans and Query Store (covered in Part 2).

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight (DP-800 Exam Prep)

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%)
   --> Optimize database performance
      --> Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight

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

One of the primary responsibilities of a SQL AI Developer is ensuring that database queries execute efficiently. Slow queries can increase response times, consume excessive CPU and memory, cause blocking, reduce scalability, and negatively affect AI-powered applications that rely on timely access to data.

The DP-800 exam expects candidates to know how to:

Analyze query execution plans
Identify inefficient query operators
Interpret estimated and actual execution plans
Use Dynamic Management Views (DMVs) to monitor performance
Use Query Store to identify and resolve performance regressions
Use Query Performance Insight in Azure SQL Database
Recommend performance improvements based on collected metrics

Why Query Performance Matters

Database performance directly affects application performance.

Poorly optimized queries can lead to:

Slow application response times
High CPU utilization
Excessive memory consumption
Long-running transactions
Locking and blocking
Deadlocks
Reduced scalability
Increased Azure SQL costs

For AI-enabled applications, inefficient queries can delay:

Retrieval-Augmented Generation (RAG)
Semantic searches
Vector searches
AI model inference
Data preparation pipelines

Performance tuning is therefore an essential database development skill.

SQL Server Query Processing

Before SQL Server executes a query, it performs several steps:

Parse the T-SQL statement
Validate syntax and object names
Optimize the query
Generate an execution plan
Execute the plan

The Query Optimizer determines the most efficient execution strategy based on:

Statistics
Available indexes
Estimated row counts
Predicate selectivity
Join order
Available memory
Parallelism

What Is an Execution Plan?

An execution plan is a graphical or textual representation of how SQL Server executes a query.

It shows:

Operators
Join methods
Index usage
Estimated cost
Actual row counts
Warnings
Parallel operations

Execution plans are among the most valuable tools for diagnosing performance issues.

Estimated vs. Actual Execution Plans

SQL Server can generate two types of execution plans.

Estimated Execution Plan

Generated before execution.

Shows:

Estimated row counts
Estimated operator costs
Chosen indexes
Join methods

Does not execute the query.

In SQL Server Management Studio (SSMS):

Display Estimated Execution Plan (Ctrl + L)

Actual Execution Plan

Generated after query execution.

Shows:

Actual row counts
Actual execution statistics
Actual execution time
Memory usage
Runtime warnings
Actual operator behavior

Enable in SSMS:

Include Actual Execution Plan (Ctrl + M)

The DP-800 exam frequently tests the distinction between estimated and actual execution plans.

Understanding Execution Plan Operators

Execution plans contain operators representing individual processing steps.

Common operators include:

Operator	Purpose
Table Scan	Reads every row in a table
Clustered Index Scan	Scans an entire clustered index
Index Seek	Efficiently locates matching rows
Key Lookup	Retrieves additional columns from a clustered index
Nested Loops	Efficient join for small result sets
Merge Join	Efficient for sorted data
Hash Match	Efficient for large unsorted datasets
Sort	Orders rows
Compute Scalar	Calculates expressions
Filter	Applies predicates

Index Seek vs. Index Scan

One of the most frequently tested concepts.

Index Seek

Efficient.

Reads only qualifying rows.

Example:

			
SELECT *
FROM Customers
WHERE CustomerID = 125;

If an index exists on CustomerID:

Execution Plan:

Index Seek

Index Scan

Reads many or all index pages.

Example:

			
SELECT *
FROM Customers
WHERE YEAR(OrderDate)=2025;

Because the function prevents index usage, SQL Server often performs an Index Scan.

A scan is not always bad. If a query retrieves most rows in a table, a scan may be the most efficient choice.

Table Scans

A table scan reads every row.

Usually indicates:

Missing indexes
Non-selective predicates
Small tables
Poor query design

Table scans on very large tables often signal optimization opportunities.

Join Operators

SQL Server selects join algorithms based on estimated costs.

Nested Loops

Best for:

Small inputs
Indexed lookups

Merge Join

Best for:

Large sorted datasets

Requires sorted input.

Hash Match

Best for:

Large unsorted datasets

Uses more memory but often performs well for analytical workloads.

Cost Percentage

Execution plans display estimated operator costs.

Example:

			
Hash Match
85%
Index Seek
10%
Sort
5%

		

Important exam point:

Cost percentages are optimizer estimates—not actual elapsed execution time.

Execution Plan Warnings

Execution plans may display warnings such as:

Missing indexes
Implicit conversions
Spills to tempdb
Missing statistics
Excessive memory grants

Warnings often identify the root cause of performance issues.

Missing Index Recommendations

Execution plans sometimes recommend indexes.

Example:

Missing Index (Impact 98%)

These recommendations can significantly improve performance but should be evaluated carefully rather than implemented automatically, because they don’t consider overall workload or maintenance costs.

Dynamic Management Views (DMVs)

DMVs expose real-time information about SQL Server’s internal state.

They are invaluable for monitoring:

Active requests
Query statistics
Index usage
Wait statistics
Sessions
Transactions
Memory usage
Cached execution plans

Common Performance DMVs

sys.dm_exec_query_stats

Provides cumulative statistics for cached query plans.

Useful columns include:

Total CPU time
Total logical reads
Total elapsed time
Execution count

Example:

			
SELECT TOP 10
    total_worker_time,
    execution_count
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

		

sys.dm_exec_sql_text()

Returns the SQL text associated with cached plans.

Often joined with:

sys.dm_exec_query_stats

sys.dm_exec_query_plan()

Returns XML execution plans.

Useful for automated analysis.

sys.dm_exec_requests

Shows currently executing requests.

Useful for identifying:

Blocking
Long-running queries
Wait types

sys.dm_exec_sessions

Shows active user sessions.

Useful for monitoring connected users.

sys.dm_os_wait_stats

Displays cumulative wait statistics.

Common waits include:

PAGEIOLATCH
CXPACKET
LCK_M_X
WRITELOG

Wait statistics often reveal the primary performance bottleneck.

sys.dm_db_index_usage_stats

Shows how indexes are used.

Helps identify:

Unused indexes
Frequently used indexes
Missing optimization opportunities

Query Store

Query Store is one of SQL Server’s most valuable performance features.

Introduced in SQL Server 2016.

It automatically captures:

Query text
Execution plans
Runtime statistics
Wait statistics
Plan history

Unlike DMVs, Query Store persists data across server restarts.

Benefits of Query Store

Query Store helps developers:

Identify slow queries
Detect regressions
Compare execution plans
Force known-good execution plans
Analyze historical performance
Monitor workload changes

It is widely used for production performance tuning.

Query Store Architecture

Query Store stores:

Query text
Multiple execution plans
Runtime statistics
Wait statistics
Historical performance

This historical information makes it much easier to diagnose intermittent issues.

Detecting Query Regressions

A query regression occurs when a query suddenly becomes slower.

Common causes include:

Updated statistics
New indexes
Parameter sniffing
Schema changes
Data growth

Query Store can compare previous and current execution plans to identify regressions.

Forcing Execution Plans

If SQL Server selects an inefficient plan, Query Store allows administrators to force a previously successful plan.

Benefits include:

Immediate performance stabilization
Reduced troubleshooting time

Forced plans should still be monitored because future schema or workload changes may make a different plan more appropriate.

Query Store Wait Statistics

Modern versions of SQL Server also capture wait statistics per query.

Examples include:

CPU waits
Lock waits
I/O waits
Memory waits

This makes troubleshooting significantly easier.

Query Performance Insight

Query Performance Insight is an Azure SQL Database performance monitoring feature available in the Azure portal.

It provides visual dashboards that display:

Top resource-consuming queries
CPU utilization
Duration
Execution count
Database workload trends
Historical performance

It simplifies performance analysis without requiring T-SQL queries.

Benefits of Query Performance Insight

Advantages include:

Visual performance analysis
Historical trends
Easy identification of expensive queries
Azure portal integration
Supports Azure SQL Database

It is especially useful for cloud database administrators.

Common Performance Problems

Missing Indexes

Symptoms:

Table scans
High logical reads

Solution:

Create appropriate indexes after evaluating workload impact.

Outdated Statistics

Symptoms:

Poor execution plans
Incorrect row estimates

Solution:

Update statistics.

UPDATE STATISTICS Sales;

Parameter Sniffing

Occurs when SQL Server caches an execution plan optimized for one parameter value that performs poorly for others.

Possible solutions include:

Query Store plan forcing
OPTION (RECOMPILE)
OPTIMIZE FOR
Query rewriting

Implicit Conversions

Example:

WHERE CustomerID='100'

if CustomerID is an integer.

Implicit conversions may prevent index seeks.

Use matching data types whenever possible.

Excessive Key Lookups

Frequent Key Lookup operators may indicate that a covering index would improve performance.

Best Practices

Use Actual Execution Plans

Actual plans reveal runtime behavior and often expose problems that estimated plans cannot.

Review Missing Index Recommendations Carefully

Evaluate:

Existing indexes
Maintenance overhead
Duplicate indexes

Do not automatically implement every recommendation.

Monitor Query Store Regularly

Review:

Regressions
Forced plans
Runtime statistics
Wait statistics

Monitor Wait Statistics

Focus on the largest waits rather than individual slow queries.

Wait analysis often identifies system-wide bottlenecks.

Update Statistics

Accurate statistics enable the optimizer to generate better execution plans.

Remove Unused Indexes

Too many indexes:

Increase storage
Slow inserts
Slow updates
Slow deletes

DMVs help identify unused indexes.

Keep Statistics Current

Automatic statistics are helpful but may not always update quickly enough for rapidly changing data.

Performance Tuning Workflow

A common performance tuning process is:

Identify a slow query.
Capture the actual execution plan.
Review Query Store history.
Check DMVs for CPU, I/O, and wait statistics.
Identify inefficient operators.
Evaluate indexing opportunities.
Update statistics if needed.
Test improvements.
Monitor results.

DP-800 Exam Tips

Remember these key points for the exam:

Actual execution plans contain runtime statistics, while estimated execution plans do not execute the query.
An Index Seek is generally more efficient than an Index Scan when retrieving a small subset of rows.
DMVs provide real-time diagnostic information but generally reset when SQL Server restarts or the execution plan cache is cleared.
Query Store retains historical query performance information across restarts.
Query Store can detect query regressions and force a previous execution plan.
Query Performance Insight provides Azure portal dashboards for Azure SQL Database performance analysis.
Execution plan cost percentages are optimizer estimates, not measurements of actual elapsed time.
Missing index recommendations should be evaluated carefully rather than applied automatically.

Practice Exam Questions

Question 1

A database administrator wants to determine how SQL Server actually executed a query, including runtime row counts and operator statistics. Which tool should be used?

A. Actual Execution Plan
B. Estimated Execution Plan
C. Query Performance Insight
D. sys.dm_db_index_usage_stats

Correct Answer: A

Explanation: The Actual Execution Plan executes the query and records runtime information such as actual row counts, memory usage, and operator performance. Estimated plans only predict how the query will execute.

Question 2

A query retrieves a single customer by using a highly selective indexed column. Which execution plan operator would typically provide the best performance?

A. Table Scan
B. Clustered Index Scan
C. Index Seek
D. Hash Match

Correct Answer: C

Explanation: An Index Seek efficiently navigates directly to the qualifying rows within an index, minimizing I/O and improving performance for selective queries.

Question 3

Which Dynamic Management View (DMV) provides cumulative performance statistics for cached query plans?

A. sys.dm_exec_query_stats
B. sys.dm_exec_sessions
C. sys.dm_db_index_usage_stats
D. sys.dm_exec_requests

Correct Answer: A

Explanation: sys.dm_exec_query_stats stores cumulative statistics such as total worker time, logical reads, elapsed time, and execution count for cached query plans.

Question 4

A developer wants to analyze historical query performance and compare execution plans before and after a deployment. Which feature should be used?

A. Activity Monitor
B. SQL Server Profiler
C. Dynamic Management Views
D. Query Store

Correct Answer: D

Explanation: Query Store stores historical query text, execution plans, runtime statistics, and wait statistics, allowing developers to compare performance across deployments.

Question 5

Which statement about Query Store is true?

A. It only stores data until SQL Server restarts.
B. It automatically captures query history and execution plans.
C. It replaces execution plans entirely.
D. It only works with Azure SQL Database.

Correct Answer: B

Explanation: Query Store automatically captures query text, execution plans, runtime statistics, and historical performance data. Unlike many DMVs, its data persists across restarts.

Question 6

Which Azure SQL Database feature provides graphical dashboards that identify high-resource queries and workload trends?

A. Database Mail
B. Query Performance Insight
C. SQL Trace
D. Extended Events

Correct Answer: B

Explanation: Query Performance Insight provides Azure portal dashboards that visualize CPU usage, query duration, execution counts, and historical performance trends.

Question 7

An execution plan displays a warning indicating a “Missing Index (Impact 96%).” What is the best course of action?

A. Immediately create the recommended index without review.
B. Ignore the recommendation because SQL Server recommendations are unreliable.
C. Evaluate the recommendation alongside the overall workload before deciding whether to implement it.
D. Rebuild every existing index first.

Correct Answer: C

Explanation: Missing index recommendations are useful starting points, but developers should consider existing indexes, maintenance overhead, and workload characteristics before implementation.

Question 8

Which situation most commonly causes an Index Scan instead of an Index Seek?

A. Searching by a primary key value
B. Filtering with a function applied to an indexed column, such as YEAR(OrderDate)
C. Using an equality predicate on an indexed column
D. Retrieving a single row by a unique index

Correct Answer: B

Explanation: Applying functions to indexed columns often makes predicates non-SARGable, preventing efficient index seeks and causing SQL Server to scan the index instead.

Question 9

A developer wants to identify currently executing queries that are waiting on locks or consuming excessive resources. Which DMV is most appropriate?

A. sys.dm_exec_requests
B. sys.dm_exec_query_plan
C. sys.dm_db_index_usage_stats
D. sys.dm_os_wait_stats

Correct Answer: A

Explanation: sys.dm_exec_requests displays currently executing requests, including wait types, blocking information, CPU usage, elapsed time, and execution status.

Question 10

Why are database statistics important for query optimization?

A. They permanently eliminate table scans.
B. They encrypt execution plans.
C. They reduce transaction log size.
D. They help the Query Optimizer estimate row counts and choose efficient execution plans.

Correct Answer: D

Explanation: SQL Server relies on statistics to estimate data distribution and row counts. Accurate statistics allow the Query Optimizer to select efficient join methods, indexes, and execution strategies, resulting in better overall query performance.

Go to the DP-800 Exam Prep Hub main page

Data Development, Databases, DP-800, SQL August 1, 2026

Preserve data integrity and consistency by using transaction isolation levels and concurrency controls (DP-800 Exam Prep)

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%)
   --> Optimize database performance
      --> Preserve data integrity and consistency by using transaction isolation levels and concurrency controls

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

A SQL AI Developer must understand how SQL Server and Azure SQL Database maintain data consistency while allowing many users and applications to access the database simultaneously. Proper use of transactions, isolation levels, row versioning, locking, and concurrency controls is critical for building scalable, high-performance, and reliable database applications.

The DP-800 exam expects candidates to understand:

Transaction ACID properties
SQL Server transaction isolation levels
Locking behavior
Row versioning
Optimistic vs. pessimistic concurrency
Deadlocks and blocking
Snapshot isolation
Read Committed Snapshot Isolation (RCSI)
Best practices for balancing performance with consistency

Why Transaction Isolation Matters

Modern applications rarely have only one user connected to a database.

Examples include:

Thousands of customers placing online orders
Banking applications processing transfers
Hospital systems updating patient records
AI applications reading operational data while transactions occur

Without concurrency controls, users could:

Read incomplete data
Overwrite each other’s changes
Produce incorrect calculations
Corrupt business data

SQL Server solves these problems through:

Transactions
Locking
Isolation levels
Versioning

Understanding Transactions

A transaction is a sequence of one or more SQL statements treated as a single unit of work.

Example:

			
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 100;
UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 200;
COMMIT;

		

If either statement fails:

ROLLBACK;

ensures neither account is changed.

ACID Properties

Every SQL transaction follows the ACID principles.

Atomicity

Everything succeeds or everything rolls back.

Example:

Money should never disappear because only one UPDATE executed.

Consistency

Database rules remain valid before and after the transaction.

Examples include:

Foreign keys
Check constraints
Unique keys
Business rules

Isolation

Concurrent transactions should not interfere improperly with one another.

Isolation levels determine exactly how much interaction is allowed.

Durability

Once committed:

data survives crashes
power failures
server restarts

SQL Server accomplishes this through the transaction log.

What Is Transaction Isolation?

Isolation controls how much one transaction can “see” changes made by another transaction.

Higher isolation:

Better consistency
More locking
Less concurrency

Lower isolation:

Higher concurrency
Better performance
Greater risk of inconsistent reads

Choosing the correct isolation level is an important design decision.

SQL Server Isolation Levels

SQL Server supports five primary isolation levels.

Isolation Level	Dirty Reads	Nonrepeatable Reads	Phantom Reads
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Snapshot	No	No	No
Serializable	No	No	No

Read Uncommitted

Lowest isolation level.

Allows reading data that has not yet been committed.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages:

Minimal locking
Highest concurrency

Disadvantages:

Dirty reads
Incorrect results
Inconsistent reporting

Equivalent to:

			
SELECT *
FROM Orders WITH (NOLOCK);

The DP-800 exam often tests that NOLOCK allows dirty reads and should not be used when data accuracy is required.

Dirty Reads

A dirty read occurs when Transaction B reads data modified by Transaction A before Transaction A commits.

Example:

Transaction A:

			
UPDATE Products
SET Price = 200;

Before commit:

Transaction B reads:

Price = 200

Transaction A rolls back.

Actual value:

Price = 100

Transaction B used data that never officially existed.

Read Committed (Default)

Default SQL Server isolation level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Characteristics:

Prevents dirty reads
Allows nonrepeatable reads
Allows phantom rows

Most OLTP applications use this level.

Nonrepeatable Reads

Occurs when:

A transaction reads the same row twice.

Another transaction updates the row between reads.

Example:

First query:

Salary = 80,000

Another transaction updates:

Salary = 90,000

Second query:

Salary = 90,000

The same row produced different values.

Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Prevents:

Dirty reads
Nonrepeatable reads

Still allows:

Phantom rows

Rows read remain locked until the transaction completes.

Phantom Reads

A phantom read occurs when:

The same query returns additional rows.

Example:

First query:

			
SELECT *
FROM Orders
WHERE Status='Pending';

Returns:

20 rows

Another transaction inserts a pending order.

Running the same query again returns:

21 rows

The extra row is called a phantom row.

Serializable

Highest isolation level.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Prevents:

Dirty reads
Nonrepeatable reads
Phantom reads

SQL Server places range locks.

Advantages:

Maximum consistency

Disadvantages:

Significant blocking
Lower throughput
Reduced scalability

Often used for:

Financial systems
Inventory management
Reservation systems

Snapshot Isolation

Snapshot Isolation uses row versioning instead of shared locks for reads.

Enable:

			
ALTER DATABASE SalesDB
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Benefits:

Readers never block writers
Writers never block readers
Consistent transaction snapshot

Ideal for:

Reporting
Analytics
AI workloads

Read Committed Snapshot Isolation (RCSI)

RCSI changes the default Read Committed behavior to use row versioning.

Enable:

			
ALTER DATABASE SalesDB
SET READ_COMMITTED_SNAPSHOT ON;

Benefits:

Greatly reduces blocking
Maintains Read Committed semantics
No application code changes required

Azure SQL Database enables RCSI by default for many workloads because it improves concurrency.

Locking

SQL Server uses locks to maintain consistency.

Common lock types include:

Lock	Purpose
Shared (S)	Reading data
Exclusive (X)	Updating data
Update (U)	Preparing to modify data
Intent (IS, IX)	Indicates lower-level locks
Schema (Sch-S, Sch-M)	Protect schema changes

Lock Granularity

Locks may occur at different levels:

Row
Page
Table
Partition
Database

SQL Server automatically chooses appropriate granularity.

Large operations may trigger lock escalation, converting many row locks into a table lock to reduce memory overhead.

Blocking

Blocking occurs when:

One transaction waits for another transaction to release its locks.

Example:

Transaction A:

			
UPDATE Products
SET Price = 50;

Transaction B:

			
SELECT *
FROM Products;

Transaction B waits until Transaction A commits.

Blocking is normal and protects consistency, but excessive blocking can reduce throughput.

Deadlocks

A deadlock occurs when:

Transaction A waits for Transaction B.

Transaction B waits for Transaction A.

Neither transaction can continue.

SQL Server automatically selects one transaction as the deadlock victim and rolls it back.

Example:

Transaction A:

Locks Table A

Needs Table B

Transaction B:

Locks Table B

Needs Table A

Result:

Deadlock.

Minimizing Deadlocks

Best practices include:

Keep transactions short.
Access tables in a consistent order.
Create proper indexes.
Avoid unnecessary user interaction inside transactions.
Commit as soon as possible.
Reduce lock duration.

Optimistic Concurrency

Optimistic concurrency assumes conflicts are uncommon.

Instead of locking rows, applications detect changes before updating.

Common implementation:

rowversion

or timestamp columns.

Example:

			
UPDATE Products
SET Price = 100
WHERE ProductID = 1
AND RowVersion = @OriginalVersion;

If zero rows are updated:

Another user modified the row first.

Pessimistic Concurrency

Assumes conflicts are likely.

Locks data immediately.

Advantages:

Prevents conflicts

Disadvantages:

More blocking
Reduced concurrency

Used in:

Banking
Airline reservations
Inventory systems

Row Versioning

Snapshot Isolation and RCSI maintain previous row versions inside tempdb (or the persisted version store in databases that support Accelerated Database Recovery).

Readers access previous committed versions without blocking writers.

Benefits include:

Improved concurrency
Reduced blocking
Better reporting performance

Transaction Best Practices

Keep Transactions Short

Avoid:

User prompts
Long loops
Waiting for external APIs

Commit Promptly

Release locks quickly.

Use Appropriate Isolation Levels

Do not always choose Serializable.

Choose the lowest level that still satisfies business requirements.

Index Frequently Queried Columns

Better indexes reduce:

Scan duration
Lock duration
Blocking

Retry Deadlock Victims

Applications should retry transactions after deadlock errors because SQL Server automatically rolls back the victim transaction.

Avoid NOLOCK for Critical Data

Dirty reads can lead to:

Incorrect reports
AI model training errors
Financial inaccuracies

Isolation Level Selection Guide

Scenario	Recommended Isolation
Financial transfers	Serializable
General OLTP	Read Committed
Reporting	Snapshot
Azure SQL workloads	RCSI
Large analytical queries	Snapshot
High-contention inventory systems	Serializable or carefully designed Repeatable Read
Temporary diagnostic queries	Read Uncommitted (use cautiously)

DP-800 Exam Tips

Remember these frequently tested points:

Read Committed is SQL Server’s default isolation level.
Dirty reads occur only under Read Uncommitted (or NOLOCK).
Snapshot Isolation uses row versioning instead of shared locks.
RCSI reduces reader/writer blocking while preserving Read Committed semantics.
Serializable provides the highest consistency but can significantly reduce concurrency.
Deadlocks occur when two or more transactions wait on each other, and SQL Server automatically selects a deadlock victim.
Optimistic concurrency commonly uses a rowversion column to detect conflicts rather than locking data.

Practice Exam Questions

Question 1

A banking application must guarantee that account balances remain accurate even when multiple users transfer funds simultaneously. Which transaction isolation level provides the highest level of protection against concurrency anomalies?

A. Read Committed
B. Snapshot
C. Serializable
D. Read Uncommitted

Correct Answer: C

Explanation: Serializable prevents dirty reads, nonrepeatable reads, and phantom reads by using range locks. It offers the highest level of transaction isolation and is well suited for critical financial operations.

Question 2

A developer executes the following statement:

SELECT * FROM Sales WITH (NOLOCK);

What behavior should the developer expect?

A. The query will prevent all concurrent updates.
B. The query may read uncommitted data.
C. The query automatically enables Snapshot Isolation.
D. The query uses Repeatable Read isolation.

Correct Answer: B

Explanation: The NOLOCK hint is equivalent to Read Uncommitted isolation and allows dirty reads, meaning rows may be read before transactions commit.

Question 3

A reporting application experiences blocking because long-running SELECT queries interfere with update operations. Which feature is most appropriate?

A. Repeatable Read
B. Serializable
C. Snapshot Isolation
D. Exclusive locking

Correct Answer: C

Explanation: Snapshot Isolation uses row versioning so readers do not block writers and writers do not block readers, making it ideal for reporting workloads.

Question 4

Which concurrency problem occurs when a transaction reads the same row twice and receives different values because another transaction updated the row?

A. Nonrepeatable read
B. Phantom read
C. Lock escalation
D. Dirty read

Correct Answer: A

Explanation: A nonrepeatable read occurs when the same row returns different values within the same transaction due to another committed update.

Question 5

What is the primary purpose of a rowversion column in optimistic concurrency control?

A. Encrypt row data
B. Compress large tables
C. Detect whether a row has changed since it was read
D. Prevent index fragmentation

Correct Answer: C

Explanation: Applications compare the original rowversion value during updates. If it has changed, another transaction modified the row, allowing the application to detect concurrency conflicts.

Question 6

Which SQL Server feature reduces reader and writer blocking while maintaining Read Committed behavior?

A. Read Committed Snapshot Isolation (RCSI)
B. Table hints
C. Lock escalation
D. Read Uncommitted

Correct Answer: A

Explanation: RCSI uses row versioning for Read Committed transactions, significantly reducing blocking without requiring application code changes.

Question 7

Two transactions each hold a lock that the other requires, causing both to wait indefinitely. What is this situation called?

A. Blocking
B. Lock escalation
C. Phantom read
D. Deadlock

Correct Answer: D

Explanation: A deadlock occurs when transactions wait on each other’s resources. SQL Server automatically selects one transaction as the deadlock victim and rolls it back.

Question 8

Which ACID property ensures that either all statements in a transaction succeed or none of them are applied?

A. Consistency
B. Isolation
C. Atomicity
D. Durability

Correct Answer: C

Explanation: Atomicity guarantees that a transaction is treated as a single unit of work. If any part fails, the entire transaction is rolled back.

Question 9

A database administrator wants to reduce the likelihood of deadlocks. Which practice is recommended?

A. Keep transactions open for longer periods.
B. Access tables in a consistent order across transactions.
C. Use Serializable isolation for every workload.
D. Disable indexes on frequently accessed tables.

Correct Answer: B

Explanation: Accessing resources in a consistent order reduces circular dependencies between transactions, decreasing the likelihood of deadlocks.

Question 10

Which statement best describes Snapshot Isolation?

A. It allows dirty reads to improve performance.
B. It relies exclusively on shared locks for readers.
C. It prevents writers from modifying data during reads.
D. It provides each transaction with a consistent version of committed data by using row versioning.

Correct Answer: D

Explanation: Snapshot Isolation stores previous committed versions of rows, allowing transactions to view a consistent snapshot of the database without blocking concurrent updates.

Go to the DP-800 Exam Prep Hub main page

Database Administration, Databases, DP-800, Microsoft Certification August 1, 2026

Recommend database configurations (DP-800 Exam Prep)

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%)
   --> Optimize database performance
      --> Recommend database configurations

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

Proper database configuration is one of the most effective ways to achieve high performance, scalability, availability, and cost efficiency. Even well-designed databases and optimized queries can perform poorly if the underlying database configuration is not appropriate for the workload.

The DP-800: Developing AI-Enabled Database Solutions exam expects candidates to understand how to recommend database configurations for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric SQL Database, and other SQL-based data platforms. Rather than simply changing code, developers should be able to identify when performance issues can be addressed through configuration changes involving compute resources, storage, memory, indexing strategies, concurrency, automatic tuning, and database compatibility settings.

A well-configured database should balance:

Performance
Scalability
Security
High availability
Cost
Maintainability

Why Database Configuration Matters

Database configuration directly affects:

Query execution speed
Transaction throughput
Concurrent user capacity
AI workload responsiveness
Resource utilization
Operational costs
System reliability

Poor configurations can result in:

Long-running queries
Excessive locking
Deadlocks
High CPU utilization
Memory pressure
Storage bottlenecks
Increased cloud costs

Understand the Workload

Before recommending a configuration, identify the workload characteristics.

Questions include:

Is the workload transactional (OLTP)?
Is it analytical (OLAP)?
Is it mixed?
Is it AI-enabled?
How many concurrent users exist?
What is the expected database size?
Is low latency required?
Are workloads predictable or bursty?

Understanding the workload guides all subsequent configuration decisions.

Choose the Appropriate SQL Platform

Microsoft offers several SQL deployment options.

SQL Server

Best for:

On-premises deployments
Complete administrative control
Highly customized environments

Developer considerations:

Hardware sizing
Memory configuration
Storage layout
Backup strategy

Azure SQL Database

Best for:

Cloud-native applications
Fully managed environments
Elastic scaling
Minimal administration

Features include:

Automatic tuning
Automatic backups
Built-in high availability
Automatic patching

Azure SQL Managed Instance

Best for:

Existing SQL Server applications
High compatibility
Managed platform
Near full SQL Server feature support

Microsoft Fabric SQL Database

Best for:

Analytics
AI-enabled workloads
Integrated Microsoft Fabric solutions
Modern cloud-native architectures

Compute Configuration

Choosing the proper compute tier significantly affects performance.

Azure SQL offers multiple purchasing models.

DTU Model

Combines:

CPU
Memory
Storage I/O

into a single performance unit.

Advantages:

Simple sizing
Easier cost estimation

Disadvantages:

Less granular control

vCore Model

Separates:

CPU
Memory
Storage

Advantages:

More flexibility
Better workload tuning
Easier migration from SQL Server

The DP-800 exam generally emphasizes the vCore model because it provides greater control over resource allocation.

Service Tiers

Azure SQL Database supports multiple service tiers.

General Purpose

Suitable for:

Typical business applications
Moderate workloads
Cost-sensitive deployments

Business Critical

Provides:

Low latency
Faster storage
Multiple replicas
High availability

Ideal for:

Mission-critical applications
High transaction workloads

Hyperscale

Designed for:

Very large databases
Rapid storage growth
Read scale-out
High-performance cloud workloads

Serverless vs. Provisioned Compute

Serverless

Advantages:

Auto-scaling
Auto-pausing
Cost savings
Ideal for intermittent workloads

Suitable for:

Development environments
Departmental applications
Variable workloads

Provisioned

Advantages:

Predictable performance
Always available
Consistent response times

Suitable for:

Production systems
High-volume applications
Mission-critical workloads

Storage Configuration

Storage performance greatly affects database responsiveness.

Recommendations include:

Premium SSD storage
Sufficient IOPS
Low latency
Adequate capacity planning

Avoid running databases near storage limits.

TempDB Configuration (SQL Server)

TempDB supports:

Temporary tables
Sort operations
Hash joins
Version store
Snapshot isolation

Best practices include:

Multiple TempDB data files
Equal file sizes
Fast storage
Proper autogrowth settings

Although Azure SQL manages TempDB automatically, understanding these concepts remains valuable.

Database Compatibility Level

SQL Server compatibility levels determine optimizer behavior and available features.

Newer compatibility levels provide:

Improved query optimization
New T-SQL features
Better cardinality estimation
Performance enhancements

However, compatibility changes should be tested because query plans may change.

Automatic Tuning

Azure SQL Database supports automatic tuning features.

These include:

CREATE INDEX
DROP INDEX
FORCE LAST GOOD PLAN

Benefits include:

Improved query performance
Reduced manual administration
Automatic regression correction

Developers should understand when automatic tuning is appropriate and how to monitor its recommendations.

Intelligent Query Processing

Recent SQL Server versions include Intelligent Query Processing (IQP).

Features include:

Memory Grant Feedback
Batch Mode on Rowstore
Scalar UDF Inlining
Table Variable Deferred Compilation
Parameter Sensitive Plan Optimization

These features improve query performance without requiring application changes.

Configure Appropriate Indexes

Configuration recommendations often involve indexing.

Common index types include:

Clustered indexes
Nonclustered indexes
Filtered indexes
Columnstore indexes
XML indexes
Spatial indexes
Full-text indexes

Recommendations depend on workload characteristics.

For example:

OLTP systems benefit primarily from clustered and nonclustered indexes, while analytical workloads often benefit from columnstore indexes.

Partition Large Tables

Partitioning improves manageability and can improve query performance when queries access only specific partitions.

Benefits include:

Faster maintenance
Improved archiving
Reduced I/O
Partition elimination

Partitioning is especially useful for:

Sales history
Audit logs
Time-series data
IoT data

Optimize Concurrency

Database configuration affects concurrent users.

Recommendations include:

Appropriate transaction isolation levels
Snapshot Isolation
Read Committed Snapshot Isolation (RCSI)
Short transactions
Efficient indexing

Reducing blocking improves application scalability.

Configure Memory Usage

Memory influences:

Buffer cache
Query execution
Sort operations
Hash joins
Plan cache

For SQL Server:

Configure:

Maximum Server Memory
Minimum Server Memory

Avoid allowing SQL Server to consume all available system memory.

Azure SQL manages memory automatically.

Configure Database Files

Best practices include:

Multiple data files for very large databases
Appropriate autogrowth settings
Fixed-size growth increments
Avoid very small autogrowth values
Separate data and log files (SQL Server)

Poor autogrowth settings can increase fragmentation.

Statistics Configuration

Query optimization depends heavily on statistics.

Recommendations include:

Enable AUTO_CREATE_STATISTICS
Enable AUTO_UPDATE_STATISTICS
Update statistics after major data changes

Outdated statistics frequently result in poor execution plans.

High Availability Configuration

Configuration should match business requirements.

Options include:

Always On Availability Groups
Azure SQL built-in HA
Geo-replication
Auto-failover groups
Read replicas

Choose configurations based on:

Recovery Time Objective (RTO)
Recovery Point Objective (RPO)

AI Workload Considerations

AI-enabled applications often perform:

Vector searches
Embedding generation
Semantic search
Retrieval-Augmented Generation (RAG)
JSON processing

Recommendations include:

Sufficient memory
Fast storage
Columnstore indexes for analytics
Azure AI Search integration
Read replicas for heavy query workloads

Monitor Before Recommending Changes

Performance recommendations should be evidence-based.

Useful monitoring tools include:

Query Store
Execution Plans
Azure Monitor
SQL Insights
Dynamic Management Views (DMVs)
Performance Dashboard
Extended Events
Intelligent Insights (Azure SQL)

Common Configuration Mistakes

Avoid:

Choosing Business Critical for low-volume applications
Underprovisioning CPU
Ignoring storage latency
Disabling automatic statistics
Excessive indexing
Using outdated compatibility levels without testing
Poor TempDB configuration
Unlimited autogrowth
Ignoring Query Store recommendations
Not monitoring workload trends

Best Practices

Size resources based on workload characteristics.
Prefer the vCore purchasing model when granular control is needed.
Enable automatic tuning where appropriate.
Monitor Query Store regularly.
Keep statistics current.
Configure indexes based on workload patterns.
Test compatibility level changes before production deployment.
Use Business Critical only when required.
Consider serverless compute for intermittent workloads.
Use Hyperscale for very large databases.
Continuously monitor performance and adjust configurations.

DP-800 Exam Tips

Remember these key points for the exam:

Understand when to recommend General Purpose, Business Critical, or Hyperscale service tiers.
Know the differences between DTU and vCore purchasing models.
Understand when serverless compute is appropriate.
Automatic tuning can create indexes, remove unused indexes, and correct query regressions.
Query Store is one of the primary tools for identifying performance problems.
Statistics and indexes are fundamental to query optimization.
Compatibility level influences the query optimizer and available SQL features.
Database recommendations should always be based on observed workload characteristics and performance metrics.

Practice Exam Questions

Question 1

A database experiences unpredictable traffic during business hours but is often idle overnight. Which Azure SQL compute option is likely to provide the best balance between performance and cost?

A. Business Critical with maximum vCores

B. Hyperscale

C. Serverless compute

D. Dedicated SQL Server on a virtual machine

Answer: C

Explanation: Serverless compute automatically scales resources and can pause during periods of inactivity, reducing costs while still supporting variable workloads.

Question 2

A company requires extremely low latency and high availability for a mission-critical online transaction processing (OLTP) application. Which Azure SQL service tier should be recommended?

A. General Purpose

B. Business Critical

C. Basic

D. Serverless

Answer: B

Explanation: Business Critical uses local SSD storage, multiple replicas, and built-in high availability, making it ideal for latency-sensitive, mission-critical workloads.

Question 3

Which Azure SQL purchasing model provides independent control over CPU, memory, and storage resources?

A. DTU

B. Elastic Pool

C. vCore

D. Consumption

Answer: C

Explanation: The vCore model allows independent configuration of compute and storage resources, making it suitable for workload-specific optimization.

Question 4

Which SQL Server feature automatically recommends creating or dropping indexes and can force the last known good execution plan?

A. SQL Server Agent

B. Query Notifications

C. Extended Events

D. Automatic Tuning

Answer: D

Explanation: Automatic Tuning can recommend and apply index changes and automatically correct certain query regressions by forcing a previously successful execution plan.

Question 5

A developer notices that query execution plans are using outdated data distribution estimates after a large data import. Which recommendation is most appropriate?

A. Disable Query Store

B. Shrink the database

C. Update database statistics

D. Reduce TempDB size

Answer: C

Explanation: Accurate statistics help the query optimizer estimate row counts correctly and generate efficient execution plans.

Question 6

Which feature should be reviewed first when investigating consistently slow queries in Azure SQL Database?

A. SQL Server Configuration Manager

B. Query Store

C. Windows Event Viewer

D. Azure Key Vault

Answer: B

Explanation: Query Store captures execution plans, runtime statistics, and query history, making it one of the best tools for diagnosing performance problems.

Question 7

A database stores several years of sales history, but most queries retrieve only recent records. Which configuration recommendation can improve performance and simplify maintenance?

A. Disable indexing

B. Reduce available memory

C. Partition the table by date

D. Increase transaction isolation to SERIALIZABLE

Answer: C

Explanation: Partitioning large tables by date enables partition elimination, reducing I/O and improving maintenance operations such as archiving.

Question 8

Which database configuration recommendation helps reduce blocking while supporting high levels of concurrent read activity?

A. Enable Read Committed Snapshot Isolation (RCSI)

B. Disable indexes

C. Increase autogrowth frequency

D. Force table scans

Answer: A

Explanation: RCSI uses row versioning, allowing readers to access consistent data without blocking writers, thereby improving concurrency.

Question 9

A development team is selecting a compatibility level for a SQL Server database. What is the primary benefit of using a newer compatibility level after proper testing?

A. It automatically encrypts all database data.

B. It enables newer query optimizer improvements and T-SQL features.

C. It eliminates the need for indexes.

D. It disables Query Store.

Answer: B

Explanation: Newer compatibility levels introduce optimizer enhancements, improved cardinality estimation, and access to newer T-SQL functionality. Testing is important because execution plans may change.

Question 10

A database administrator configures SQL Server with unrestricted memory usage on a shared server hosting several applications. What is the most likely recommendation?

A. Continue using the default settings.

B. Increase TempDB file count only.

C. Disable automatic statistics.

D. Configure Maximum Server Memory to reserve memory for the operating system and other applications.

Answer: D

Explanation: Configuring Maximum Server Memory prevents SQL Server from consuming all available system memory, helping maintain overall server stability and ensuring sufficient resources remain available for the operating system and other applications.

Go to the DP-800 Exam Prep Hub main page

Data Governance, Data Security, Databases, DP-800, Microsoft Certification, security August 1, 2026

Secure model endpoints, including Managed Identity (DP-800 Exam Prep)

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%)
   --> Implement data security and compliance
      --> Secure model endpoints, including Managed Identity

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

As organizations increasingly integrate Artificial Intelligence (AI) into database applications, protecting AI model endpoints has become a critical security requirement. AI-enabled SQL applications frequently invoke external AI services such as Azure OpenAI, Azure AI Foundry models, Azure AI Search, Azure Machine Learning endpoints, and custom REST APIs. These services often process sensitive business data, making endpoint security an important aspect of application architecture.

The DP-800 certification expects candidates to understand how to securely authenticate applications to AI services without exposing secrets. Microsoft recommends using Microsoft Entra ID (formerly Azure Active Directory) and Managed Identities whenever possible instead of storing passwords or API keys.

A major focus of the exam is understanding how SQL applications securely communicate with external AI services while following the Zero Trust security model.

Why AI Model Endpoints Must Be Secured

An AI model endpoint is the network endpoint that applications call to perform AI operations such as:

Text generation
Embedding generation
Semantic search
Retrieval-Augmented Generation (RAG)
Classification
Summarization
Vector similarity searches

Because endpoint requests frequently contain:

Customer information
Financial records
Healthcare data
Intellectual property
Confidential business documents

Unauthorized access can lead to:

Data leakage
Unauthorized AI usage
Excessive Azure costs
Compliance violations
Prompt injection attacks
Credential theft

Therefore, authentication and authorization are essential.

Authentication Options for AI Endpoints

Microsoft AI services generally support multiple authentication mechanisms.

Authentication Method	Recommended	Notes
API Keys	Good	Simple but secrets must be managed
Microsoft Entra ID	Excellent	Preferred for enterprise environments
Managed Identity	Best	Eliminates secret management
Service Principals	Very Good	Used for applications outside Azure
OAuth Tokens	Good	Short-lived secure tokens

For DP-800, Managed Identity is the preferred authentication method whenever available.

Understanding Managed Identity

A Managed Identity is an automatically managed identity in Microsoft Entra ID that Azure creates for an Azure resource.

Instead of storing:

passwords
connection strings
API keys
client secrets

the Azure platform authenticates on behalf of the application.

Examples of Azure resources supporting Managed Identity include:

Azure SQL Database
Azure SQL Managed Instance
Azure App Service
Azure Functions
Azure Container Apps
Azure Kubernetes Service
Azure Virtual Machines
Azure Data Factory
Azure Logic Apps
Azure Machine Learning

Types of Managed Identity

There are two types.

System-Assigned Managed Identity

Characteristics:

Created automatically
One identity per Azure resource
Deleted automatically with the resource
Cannot be shared

Example:

Azure Function → One Managed Identity

If the Function App is deleted:

Identity is deleted automatically.

User-Assigned Managed Identity

Characteristics:

Independent Azure resource
Can be assigned to multiple services
Exists after applications are deleted
Easier to reuse across environments

Example:

One User-Assigned Identity may be used by:

Azure Function
Azure App Service
Azure SQL Managed Instance
Azure Container App

This simplifies permission management.

Benefits of Managed Identity

Managed Identity provides several important advantages.

No Secret Management

Developers no longer store:

passwords
API keys
client secrets
certificates

This significantly reduces security risks.

Automatic Credential Rotation

Azure rotates credentials automatically.

Developers never need to:

renew certificates
rotate passwords
update connection strings

Reduced Attack Surface

Secrets stored in:

source code
configuration files
GitHub repositories
CI/CD pipelines

are eliminated.

Improved Compliance

Managed Identity helps organizations meet:

SOC
ISO
HIPAA
GDPR
PCI DSS

security recommendations.

Fine-Grained Access Control

Permissions are assigned through Azure Role-Based Access Control (RBAC).

Applications receive only the permissions they require.

Authentication Flow Using Managed Identity

A typical authentication sequence is:

Azure resource requests an access token.
Azure Instance Metadata Service validates the request.
Microsoft Entra ID issues an OAuth access token.
Application sends the token to the AI endpoint.
Azure AI service validates the token.
Request is processed.

No passwords or API keys are exchanged.

Using Managed Identity with Azure OpenAI

Instead of:

API Key

Applications can authenticate using:

Bearer Token

obtained through Managed Identity.

The application requests an OAuth token for the Azure OpenAI resource and includes it in the HTTP Authorization header.

Advantages include:

no API key storage
centralized identity management
automatic credential rotation
Azure RBAC integration

Managed Identity with Azure AI Search

Azure AI Search supports Microsoft Entra authentication.

Applications using Managed Identity can:

create indexes
query indexes
update indexes
execute semantic search
perform vector search

Access permissions are controlled using Azure RBAC rather than shared administrative keys.

Managed Identity with Azure SQL Database

SQL applications may access AI services.

Example workflow:

Azure SQL Stored Procedure

↓

External Application

↓

Managed Identity

↓

Azure OpenAI

↓

Generated Response

No API keys are embedded anywhere.

Securing Azure AI Foundry Models

Azure AI Foundry endpoints also support Microsoft Entra authentication.

Best practices include:

Disable anonymous access.
Use Managed Identity where supported.
Restrict endpoint access with RBAC.
Enable private networking.
Monitor endpoint usage.
Enable diagnostic logging.

Azure Role-Based Access Control (RBAC)

Authentication identifies who is making the request.

Authorization determines what they can do.

Azure RBAC assigns permissions using roles.

Common roles include:

Cognitive Services User
Cognitive Services Contributor
Search Service Contributor
Search Index Data Reader
Search Index Data Contributor

Assign the minimum permissions required.

Principle of Least Privilege

Applications should receive only the permissions necessary to perform their tasks.

For example:

Application that generates embeddings:

Needs:

Generate embeddings

Does NOT need:

Delete deployment
Create deployments
Manage subscriptions

This reduces the impact of compromised credentials.

Private Endpoints

Many Azure AI services support Azure Private Link.

Benefits include:

Private IP addresses
No public internet exposure
Reduced attack surface
Simplified firewall rules
Secure communication within Azure Virtual Networks

Private Endpoints are strongly recommended for production deployments handling sensitive data.

Network Security

Additional protections include:

Azure Firewall
Network Security Groups
IP restrictions
Virtual Networks
Private DNS Zones
Azure DDoS Protection

These layers complement identity-based security.

Monitoring AI Endpoint Usage

Organizations should continuously monitor:

Authentication failures
Unauthorized access attempts
High request volumes
Geographic anomalies
Excessive token usage
API throttling
Unusual costs

Useful monitoring services include:

Azure Monitor
Azure Activity Log
Azure Log Analytics
Microsoft Defender for Cloud
Microsoft Sentinel

Secure Secrets That Cannot Be Eliminated

Some scenarios still require secrets.

Store them in:

Azure Key Vault

Never store secrets in:

source code
Git repositories
application settings
SQL tables
configuration files

Common Security Mistakes

Avoid:

Hardcoding API keys
Sharing one API key among multiple applications
Granting Contributor rights unnecessarily
Disabling authentication
Using long-lived secrets
Storing credentials in GitHub
Ignoring endpoint monitoring
Using public endpoints for sensitive workloads

DP-800 Exam Tips

Remember these key points:

Managed Identity is Microsoft’s preferred authentication mechanism for Azure-hosted applications.
Managed Identity eliminates the need to store secrets.
Microsoft Entra ID provides identity and authentication.
Azure RBAC provides authorization.
Use Private Endpoints for production AI workloads whenever possible.
Follow the Principle of Least Privilege.
Monitor AI endpoint activity using Azure Monitor and Microsoft Sentinel.
Store unavoidable secrets in Azure Key Vault.
Prefer token-based authentication over API keys.

Practice Exam Questions

Question 1

A development team wants an Azure Function to securely access an Azure OpenAI endpoint without storing credentials. Which authentication method should be recommended?

A. SQL Authentication

B. API Key stored in configuration

C. System-assigned Managed Identity

D. Windows Authentication

Answer: C

Explanation:
A system-assigned Managed Identity allows the Azure Function to authenticate with Microsoft Entra ID without storing credentials. This is Microsoft’s recommended approach for Azure-hosted services.

Question 2

Which statement best describes Microsoft Entra ID in relation to AI endpoints?

A. It encrypts AI model outputs.

B. It provides identity and authentication services.

C. It compresses prompt data.

D. It performs semantic search.

Answer: B

Explanation:
Microsoft Entra ID authenticates users, services, and applications, issuing access tokens that AI services validate before granting access.

Question 3

Which Azure feature automatically rotates credentials used by applications?

A. Azure Firewall

B. Azure Key Vault

C. Private Endpoint

D. Managed Identity

Answer: D

Explanation:
Managed Identity automatically manages and rotates credentials, eliminating manual secret rotation.

Question 4

Which Azure service should be used to securely store secrets when Managed Identity cannot be used?

A. Azure Blob Storage

B. Azure Files

C. Azure Key Vault

D. Azure Monitor

Answer: C

Explanation:
Azure Key Vault securely stores secrets, certificates, and keys, making it the preferred repository for credentials that cannot be eliminated.

Question 5

What is the primary purpose of Azure RBAC?

A. Encrypt data at rest

B. Assign authorization permissions to authenticated identities

C. Compress AI embeddings

D. Improve query performance

Answer: B

Explanation:
Azure RBAC controls which actions authenticated users, applications, and services can perform on Azure resources.

Question 6

An organization wants AI model traffic to remain entirely within its Azure virtual network. Which feature should be implemented?

A. API Management

B. Azure CDN

C. Private Endpoint

D. Azure Backup

Answer: C

Explanation:
Private Endpoints expose Azure services through private IP addresses within a virtual network, preventing traffic from traversing the public internet.

Question 7

Which authentication approach most reduces the risk of credential exposure?

A. Hard-coded API keys

B. Shared service accounts

C. Managed Identity

D. SQL logins

Answer: C

Explanation:
Managed Identity removes the need to store credentials in application code or configuration, significantly reducing the attack surface.

Question 8

What security principle recommends granting only the permissions an application requires?

A. Defense in Depth

B. Zero Downtime

C. Fail Fast

D. Principle of Least Privilege

Answer: D

Explanation:
The Principle of Least Privilege minimizes security risks by limiting permissions to only those necessary for a specific task.

Question 9

Which service is most appropriate for monitoring authentication failures and unusual AI endpoint activity?

A. Azure Monitor

B. Azure DNS

C. Azure Bastion

D. Azure Disk Storage

Answer: A

Explanation:
Azure Monitor collects logs, metrics, and alerts that help detect authentication failures, unusual access patterns, and operational issues affecting AI services.

Question 10

A company currently authenticates to Azure OpenAI using API keys embedded in application configuration files. What is the best modernization recommendation?

A. Store the API key in a SQL table.

B. Replace API keys with Managed Identity authentication whenever supported.

C. Increase the API key expiration period.

D. Share a single API key across all applications.

Answer: B

Explanation:
Replacing API keys with Managed Identity improves security by eliminating stored secrets, enabling automatic credential management, and integrating with Microsoft Entra ID and Azure RBAC.

Go to the DP-800 Exam Prep Hub main page