Tag: Microsoft Certification

Data Development, Data Governance, Databases, DP-800, Microsoft Certification

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

or

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

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:

  1. Initialize Git.
  2. Create the repository.
  3. Commit the initial project.
  4. Push to GitHub or Azure DevOps.
  5. 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

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:

  1. Connects to an existing SQL Server or Azure SQL Database.
  2. Reads the database schema.
  3. Extracts supported objects.
  4. Creates corresponding .sql files.
  5. 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:

  1. Parse SQL files
  2. Validate syntax
  3. Resolve dependencies
  4. Build the database model
  5. Detect conflicts
  6. 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

WarningError
Build succeedsBuild fails
Potential issueMust be fixed
Deployment possibleDeployment blocked
Review recommendedImmediate 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

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 DevelopmentSQL Database Projects
Direct changes in SSMSChanges made in project files
Difficult to track historyFull Git history
Manual deploymentsAutomated deployments
Hard to validateBuild-time validation
Production-first changesDevelopment-first workflow
Error detection during deploymentError 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:

  1. Create project
  2. Choose SQL Server platform
  3. Add database objects
  4. Build project
  5. Resolve validation errors
  6. Generate DACPAC
  7. 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:

TraditionalSDK-Style
Large XMLMinimal XML
Manual file inclusionAutomatic discovery
Windows-focusedCross-platform
Older MSBuildModern SDK
More maintenanceLess maintenance
Limited CLI supportExcellent 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-TimeRuntime
During buildDuring execution
Finds schema errors earlyErrors appear after deployment
Faster troubleshootingProduction outages possible
Safer deploymentsHigher 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, Data Governance, Data Strategy, DP-800, Microsoft Certification

Create and manage reference/static data in source control (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 and manage reference/static data in source control

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 database solution consists of more than just tables, views, stored procedures, and security objects. Many applications also depend on reference data (sometimes called lookup data) or static data that rarely changes but is essential for application functionality.

Examples include:

  • Country codes
  • Currency codes
  • Product categories
  • Sales territories
  • Tax rates
  • Department lists
  • User roles
  • Status codes
  • ISO language codes

In modern DevOps practices, this data should be managed alongside the database schema using source control. Keeping reference data under version control ensures that every environment—development, testing, staging, and production—contains the correct data required by the application.

For the DP-800 exam, Microsoft expects candidates to understand how to manage static data within SQL Database Projects and CI/CD pipelines, including deployment strategies, version control practices, and synchronization techniques.

What Is Reference (Static) Data?

Reference data is information that changes infrequently and is used repeatedly by applications to enforce consistency and business rules.

Examples include:

TableExample Values
CountriesUSA, Canada, Mexico
OrderStatusPending, Processing, Shipped
PaymentTypesCash, Credit Card, ACH
DepartmentsSales, HR, Finance
PriorityLevelsLow, Medium, High

Unlike transactional data, reference data is generally created by administrators rather than users.

Characteristics of Reference Data

Reference data is typically:

  • Small in volume
  • Read frequently
  • Updated infrequently
  • Shared across applications
  • Required for business logic
  • Consistent across environments

Because it changes rarely, it is well suited for storage in source control.

What Is Source Control?

Source control (also called version control) tracks changes to files over time.

Common source control systems include:

  • Git
  • Azure Repos
  • GitHub
  • GitLab

Within SQL Database Projects, source control stores:

  • Database schema
  • Stored procedures
  • Views
  • Functions
  • Security objects
  • Deployment scripts
  • Reference data scripts

Why Store Reference Data in Source Control?

Managing static data in source control provides several benefits:

  • Consistent deployments
  • Reproducible environments
  • Complete change history
  • Easier collaboration
  • Simplified rollback
  • Automated deployments
  • Reduced configuration drift

Without version-controlled reference data, development and production environments can become inconsistent.

Configuration Data vs. Reference Data

Candidates should understand the distinction.

Reference Data

Business information used by applications.

Examples:

  • Product categories
  • Country codes
  • Payment methods

Configuration Data

Controls application behavior.

Examples:

  • Feature flags
  • Connection settings
  • API endpoints
  • Retry counts

Configuration data often differs between environments, while reference data should usually remain identical.

Examples of Reference Data

Country table:

CREATE TABLE dbo.Country
(
    CountryCode CHAR(2) PRIMARY KEY,
    CountryName NVARCHAR(100)
);

Static data:

INSERT INTO dbo.Country
VALUES
('US','United States'),
('CA','Canada'),
('MX','Mexico');

This script can be committed to Git and deployed automatically.

Why Not Manually Populate Lookup Tables?

Manual updates introduce problems:

  • Human error
  • Missing rows
  • Environment inconsistencies
  • Forgotten updates
  • Difficult auditing

Automated deployment eliminates these risks.

Reference Data in SQL Database Projects

SQL Database Projects primarily manage schema objects.

Reference data is commonly deployed using:

  • Post-deployment scripts
  • SQLCMD scripts
  • Seed scripts
  • Data synchronization scripts

The database schema and required reference data become part of one deployment process.

Post-Deployment Scripts

A post-deployment script runs after the DACPAC deployment completes.

Typical uses include:

  • Insert lookup values
  • Seed tables
  • Create administrative users
  • Initialize configuration

Example:

:r .\SeedData\Countries.sql
:r .\SeedData\OrderStatus.sql
:r .\SeedData\Departments.sql

Each referenced script inserts the required static data.

Organizing Seed Data

A common project structure:

DatabaseProject
│
├── Tables
├── Views
├── Procedures
├── Security
├── PostDeployment
│
├── SeedData
│      Countries.sql
│      States.sql
│      PaymentTypes.sql
│      StatusCodes.sql
│
└── Scripts

Keeping seed data in dedicated folders improves maintainability.

Idempotent Seed Scripts

A deployment may execute multiple times.

Therefore, seed scripts should be idempotent, meaning they can run repeatedly without producing duplicate data.

Instead of:

INSERT INTO Status
VALUES ('Pending');

Use:

IF NOT EXISTS
(
    SELECT 1
    FROM dbo.Status
    WHERE StatusName='Pending'
)
INSERT INTO dbo.Status(StatusName)
VALUES ('Pending');

Running this script multiple times inserts only one row.

Using MERGE for Synchronization

Another common approach is the MERGE statement.

Example:

MERGE dbo.Status AS Target
USING
(
VALUES
('Pending'),
('Shipped'),
('Delivered')
) AS Source(StatusName)
ON Target.StatusName=Source.StatusName
WHEN NOT MATCHED THEN
INSERT(StatusName)
VALUES(Source.StatusName);

MERGE synchronizes reference data without creating duplicates.

Exam Tip: While MERGE is powerful, it has historically had edge cases in SQL Server. Many organizations still use it for static data synchronization, but others prefer separate INSERT, UPDATE, and DELETE statements for greater predictability. Understand both approaches for the exam.

Updating Existing Reference Data

Sometimes lookup values change.

Example:

UPDATE dbo.Country
SET CountryName='United States of America'
WHERE CountryCode='US';

These changes should be committed to source control so all environments receive the update.

Removing Reference Data

Occasionally obsolete values must be removed.

Example:

DELETE
FROM dbo.Status
WHERE StatusName='Obsolete';

Deletion scripts should be carefully reviewed to avoid breaking foreign key relationships.

Versioning Static Data

Reference data evolves over time.

Example:

Version 1

Pending
Shipped
Delivered

Version 2

Pending
Processing
Shipped
Delivered
Cancelled

Git records exactly when each change occurred.

Source Control Workflow

Typical workflow:

Developer updates lookup table
↓
Commit to Git
↓
Pull Request
↓
Code Review
↓
Merge
↓
CI Build
↓
Deploy to Test
↓
Validate
↓
Deploy to Production

This ensures every environment receives the same approved changes.

Reference Data and CI/CD

During deployment:

Build SQL Project
↓
Create DACPAC
↓
Deploy Schema
↓
Run Post-Deployment Scripts
↓
Insert Reference Data
↓
Run Automated Tests
↓
Publish

Reference data becomes part of the deployment pipeline.

Environment Consistency

One major objective of CI/CD is ensuring environments remain synchronized.

For example:

Development

Status
Pending
Processing
Delivered

Testing

Status
Pending
Processing
Delivered

Production

Status
Pending
Processing
Delivered

All environments should contain identical lookup values unless environment-specific configuration is intentionally required.

Reference Data vs. Transactional Data

Reference DataTransactional Data
SmallLarge
Rarely changesConstantly changes
Stored in source controlNot stored in source control
Shared across environmentsEnvironment-specific
Seeded during deploymentGenerated by users

Examples of transactional data include:

  • Orders
  • Customers
  • Invoices
  • Payments
  • Audit logs

Transactional data should not be committed to Git.

Handling Sensitive Data

Reference data should generally not contain:

  • Passwords
  • API keys
  • Secrets
  • Tokens
  • Personally identifiable information (PII)

Secrets should instead be stored in secure solutions such as:

  • Azure Key Vault
  • GitHub Secrets
  • Azure DevOps Library
  • Environment variables

Best Practices

Microsoft recommends:

  • Store lookup data in source control.
  • Keep seed scripts idempotent.
  • Separate schema from reference data.
  • Use post-deployment scripts.
  • Automate deployments.
  • Review reference data changes through pull requests.
  • Avoid manual production updates.
  • Keep environments synchronized.
  • Never store secrets in source control.
  • Test deployment scripts before production.

Common DP-800 Exam Tips

Remember these key points:

TopicKey Point
Reference DataBusiness lookup data that changes infrequently
Transactional DataUser-generated operational data
Source ControlTracks schema and reference data changes
Post-Deployment ScriptCommon method for seeding reference data
Idempotent ScriptSafe to execute multiple times
MERGESynchronizes source and target data
DACPACDeploys schema, not business data by itself
GitStores scripts and deployment history
CI/CDAutomates schema and reference data deployment
SecretsShould be stored outside source control

Summary

Reference (static) data is essential to many database applications and should be managed with the same discipline as database schema. By storing lookup data scripts in source control, developers can ensure consistent deployments across all environments, maintain a complete audit history of changes, and automate data seeding as part of CI/CD pipelines. SQL Database Projects commonly use post-deployment scripts, idempotent SQL, and MERGE statements to deploy and synchronize static data safely. Understanding how reference data differs from transactional and configuration data—and how to manage it securely—is an important objective for the DP-800 certification exam.

Practice Exam Questions

Question 1

A development team wants every deployment to automatically populate the Country lookup table with approved values. What is the recommended approach when using SQL Database Projects?

A. Manually insert the rows after each deployment.

B. Store the data in a post-deployment script under source control.

C. Copy the table directly from the production database.

D. Require application users to populate the table during startup.

Answer: B

Explanation:
Post-deployment scripts are the recommended mechanism for deploying reference data with SQL Database Projects. Keeping these scripts in source control ensures consistency across all environments.

Question 2

Which type of data is most appropriate to store in source control along with a SQL Database Project?

A. Customer orders

B. Audit logs

C. Country codes

D. User transaction history

Answer: C

Explanation:
Country codes are classic reference data that changes infrequently and should be version-controlled. Transactional data such as orders and audit logs should not be stored in source control.

Question 3

Why should reference data deployment scripts be idempotent?

A. To improve query performance.

B. To ensure they can be executed repeatedly without creating duplicate data.

C. To encrypt lookup tables.

D. To automatically generate indexes.

Answer: B

Explanation:
Idempotent scripts produce the same result regardless of how many times they are executed, preventing duplicate rows during repeated deployments.

Question 4

A SQL Database Project deploys successfully, but required lookup values are missing from several tables. Which deployment component was most likely omitted?

A. Database backups

B. Execution plans

C. Post-deployment scripts

D. Statistics updates

Answer: C

Explanation:
DACPAC deployments primarily create schema objects. Reference data is typically inserted through post-deployment scripts.

Question 5

Which statement best describes reference data?

A. It is typically small, shared across environments, and changes infrequently.

B. It changes frequently throughout the day.

C. It consists primarily of user-generated transactional records.

D. It should never be stored in Git.

Answer: A

Explanation:
Reference data is stable business information, such as lookup values, that is commonly deployed to every environment through automated processes.

Question 6

Which SQL statement is commonly used to synchronize source and target reference data during deployment?

A. TRUNCATE

B. ALTER

C. EXECUTE

D. MERGE

Answer: D

Explanation:
MERGE compares source and target data, allowing inserts, updates, and optional deletes within a single statement, making it useful for synchronizing static data.

Question 7

Which item should not typically be stored in source control with reference data scripts?

A. Country lookup values

B. Department codes

C. API keys and passwords

D. Payment status values

Answer: C

Explanation:
Sensitive information such as API keys, passwords, and secrets should be stored securely in services like Azure Key Vault or GitHub Secrets rather than in source control.

Question 8

What is the primary benefit of storing reference data scripts in Git?

A. They provide version history, collaboration, and consistent deployments.

B. They eliminate the need for backups.

C. They reduce database storage requirements.

D. They automatically improve query performance.

Answer: A

Explanation:
Source control provides change tracking, collaboration, auditing, rollback capabilities, and consistent deployments across environments.

Question 9

Which data type would not normally be considered reference data?

A. Order status values

B. Customer invoices

C. Currency codes

D. Sales regions

Answer: B

Explanation:
Customer invoices are transactional records generated during business operations. They should not be managed as static reference data.

Question 10

A development team wants development, testing, and production environments to contain identical lookup values after every deployment. Which DevOps practice best supports this goal?

A. Manually editing lookup tables after deployment.

B. Importing production backups into every environment.

C. Storing reference data scripts in source control and executing them automatically during CI/CD.

D. Allowing each environment to maintain its own independent lookup values.

Answer: C

Explanation:
Automating the deployment of version-controlled reference data ensures that all environments remain synchronized and eliminates manual configuration drift.

Go to the DP-800 Exam Prep Hub main page

Data Development, Data Governance, Data Quality Assurance, DP-800, Microsoft Certification

Design and implement a testing strategy, including unit tests and integration tests (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
      --> Design and implement a testing strategy, including unit tests and integration tests

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 successful database solution is not simply one that deploys successfully—it must also behave correctly, consistently, securely, and efficiently after deployment. As organizations increasingly adopt DevOps and Continuous Integration/Continuous Deployment (CI/CD) practices, automated database testing has become a critical component of modern database development.

For the DP-800 exam, Microsoft expects candidates to understand how testing fits into SQL Database Projects, Azure DevOps, GitHub Actions, and database deployment pipelines. Candidates should understand the differences between unit testing, integration testing, regression testing, performance testing, and validation testing, as well as how automated testing reduces deployment risk.

Why Database Testing Matters

Database testing helps ensure that:

  • Database objects compile successfully.
  • Business logic returns correct results.
  • Schema changes don’t break existing applications.
  • Stored procedures function correctly.
  • Data integrity is maintained.
  • Performance remains acceptable after changes.
  • Security settings remain intact.
  • Deployment scripts execute successfully.

Without testing, even small schema changes can introduce:

  • Broken stored procedures
  • Invalid foreign keys
  • Data corruption
  • Performance regressions
  • Failed deployments
  • Security vulnerabilities

Testing is therefore an essential component of every CI/CD pipeline.

Database Testing in a CI/CD Pipeline

A typical SQL Database Project pipeline follows this workflow:

Developer writes code
        ↓
Commit to Git repository
        ↓
Continuous Integration (Build)
        ↓
Automated Unit Tests
        ↓
Build DACPAC
        ↓
Deploy to Test Environment
        ↓
Integration Tests
        ↓
Performance Validation
        ↓
User Acceptance Testing (UAT)
        ↓
Production Deployment

Testing occurs at multiple stages to detect issues as early as possible.

Types of Database Testing

Several testing categories appear throughout Microsoft’s documentation.

Testing TypePurpose
Unit TestingTests individual database objects
Integration TestingTests interactions between components
Regression TestingEnsures previous functionality still works
Performance TestingMeasures execution speed and scalability
Load TestingMeasures behavior under heavy workload
Security TestingValidates permissions and security
Smoke TestingBasic validation after deployment
Acceptance TestingConfirms business requirements are met

The DP-800 exam primarily focuses on unit testing and integration testing.

Unit Testing

What is Unit Testing?

A unit test verifies one small, isolated piece of functionality.

Examples include testing:

  • One stored procedure
  • One scalar function
  • One trigger
  • One view
  • One computed column
  • One business rule

A unit test should focus on only one object or behavior.

Characteristics of Good Unit Tests

Good unit tests are:

  • Small
  • Fast
  • Independent
  • Repeatable
  • Automated
  • Deterministic

A unit test should produce the same result every time it runs.

Example

Stored procedure:

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerID INT
AS
SELECT *
FROM Sales.Orders
WHERE CustomerID = @CustomerID;

Unit test verifies:

  • Valid customer returns rows
  • Invalid customer returns zero rows
  • NULL parameter handled correctly
  • Correct columns returned

Benefits of Unit Testing

Advantages include:

  • Finds bugs early
  • Easier debugging
  • Faster deployments
  • Lower maintenance costs
  • Safer code refactoring
  • Better documentation

Unit Testing Frameworks

Common SQL testing frameworks include:

  • tSQLt
  • SQL Test (Redgate)
  • SSDT Test Framework
  • Azure DevOps automated scripts
  • GitHub Actions test execution

Microsoft commonly demonstrates automated testing using SQL scripts executed within Azure DevOps or GitHub Actions.

tSQLt Overview

tSQLt is an open-source unit testing framework for SQL Server.

It provides:

  • Assertions
  • Test isolation
  • Mock objects
  • Fake tables
  • Automated execution

Example:

EXEC tSQLt.AssertEquals

Although DP-800 is not centered on tSQLt syntax, understanding that SQL unit testing frameworks exist is beneficial.

Integration Testing

What is Integration Testing?

Integration testing verifies that multiple database components work together correctly.

Examples:

  • Stored procedure updates multiple tables
  • Trigger fires correctly
  • Views join tables properly
  • API writes data successfully
  • ETL process loads data correctly
  • Application interacts with SQL database

Unlike unit testing, integration testing validates interactions rather than isolated components.

Example

Customer places an order.

Integration test validates:

Application
      ↓
Stored Procedure
      ↓
Orders Table
      ↓
Inventory Table
      ↓
Audit Table
      ↓
Email Queue

Every component must function correctly.

Differences Between Unit and Integration Testing

Unit TestingIntegration Testing
Tests one objectTests multiple objects
FastSlower
IsolatedEnd-to-end
Few dependenciesMultiple dependencies
Easier debuggingMore complex debugging
Developer-focusedSystem-focused

Regression Testing

Regression testing verifies that new changes have not broken existing functionality.

Example:

Version 1:

Customer search works

Developer adds:

Email search

Regression testing verifies:

  • Customer search still works
  • Existing reports still function
  • Existing APIs still work

Regression tests are especially important before production deployments.

Smoke Testing

Smoke tests perform basic validation after deployment.

Typical smoke tests include:

  • Database accessible
  • Tables exist
  • Stored procedures compile
  • Views execute
  • Security objects exist
  • Basic queries succeed

Smoke tests determine whether further testing should continue.

Performance Testing

Performance testing validates:

  • Query execution time
  • Resource utilization
  • Index efficiency
  • Blocking
  • Deadlocks
  • Response time

Performance testing frequently uses:

  • Query Store
  • Execution Plans
  • DMVs
  • Extended Events

Performance testing should be included before production deployments.

Load Testing

Load testing measures behavior under expected workloads.

Examples:

  • 100 users
  • 1,000 users
  • 10,000 concurrent requests

Metrics include:

  • CPU utilization
  • Memory consumption
  • Wait statistics
  • Response times
  • Throughput

Security Testing

Security testing validates:

  • Authentication
  • Authorization
  • Row-Level Security
  • Dynamic Data Masking
  • Always Encrypted
  • Object permissions
  • Managed Identity access

Examples:

Verify:

SalesUser

cannot access

HR.EmployeeSalary

Test Environments

Testing should occur in multiple environments.

Typical environments:

Development
↓
Build
↓
Testing
↓
Quality Assurance
↓
User Acceptance Testing
↓
Production

Each environment validates progressively more realistic scenarios.

Test Data

Reliable testing requires reliable data.

Test data should be:

  • Predictable
  • Repeatable
  • Isolated
  • Representative
  • Non-production whenever possible

Avoid using sensitive production data unless properly masked.

Database Mocks

Sometimes dependencies should be replaced.

Examples include:

  • Fake tables
  • Mock services
  • Test APIs
  • Sample datasets

Mocking allows tests to run independently.

Test Automation

Automated testing is one of the primary goals of CI/CD.

Benefits include:

  • Faster feedback
  • Consistent execution
  • Reduced human error
  • Repeatability
  • Higher deployment confidence

Automation should execute every time code changes.

Testing in Azure DevOps

Typical Azure DevOps pipeline:

Commit
↓
Build SQL Project
↓
Run Unit Tests
↓
Generate DACPAC
↓
Deploy Test Database
↓
Run Integration Tests
↓
Publish Results
↓
Approve Deployment
↓
Production

Failed tests should stop the deployment pipeline.

Testing in GitHub Actions

GitHub Actions workflows often include:

  • Build SQL Database Project
  • Create DACPAC
  • Deploy temporary database
  • Execute SQL scripts
  • Run automated tests
  • Publish results
  • Clean up environment

This supports fully automated DevOps workflows.

Continuous Testing

Continuous testing means testing occurs automatically throughout the development lifecycle.

Benefits:

  • Earlier defect detection
  • Lower costs
  • Faster releases
  • Improved quality
  • Better developer confidence

Test Coverage

Good test coverage includes:

  • CRUD operations
  • Stored procedures
  • Functions
  • Triggers
  • Views
  • Constraints
  • Security
  • Error handling
  • Transactions
  • Performance

Higher coverage reduces deployment risk.

Best Practices

Microsoft recommends:

  • Automate all repeatable tests.
  • Keep tests independent.
  • Use source control for test scripts.
  • Execute tests during every build.
  • Use representative test data.
  • Separate unit and integration tests.
  • Include regression tests.
  • Test deployment scripts.
  • Fail deployments when tests fail.
  • Review test results regularly.

Common DP-800 Exam Tips

Remember these key distinctions:

TopicKey Point
Unit TestTests one database object
Integration TestTests interaction among components
Regression TestVerifies existing functionality remains intact
Smoke TestBasic deployment validation
Performance TestMeasures speed and scalability
Load TestMeasures behavior under heavy usage
Security TestValidates permissions and protection
CI/CDAutomates builds, tests, and deployments
SQL Database ProjectSupports automated testing before deployment
DACPACDatabase deployment artifact that should be validated through automated testing

Summary

For the DP-800 exam, you should understand how testing strategies improve the reliability of SQL database deployments. Unit tests validate individual database objects, while integration tests verify interactions between multiple components. Automated testing is a fundamental part of CI/CD pipelines using SQL Database Projects, Azure DevOps, and GitHub Actions. A comprehensive testing strategy should also include regression, smoke, performance, load, and security testing. By automating tests and executing them during every build and deployment, organizations can reduce deployment risk, improve database quality, and deliver changes with greater confidence.

Practice Exam Questions

Question 1

A database developer wants to verify that a single stored procedure returns the correct results for various input values without involving other database objects. Which type of testing should be used?

A. Load testing

B. Integration testing

C. Unit testing

D. Regression testing

Answer: C

Explanation:
Unit testing validates a single database object or piece of logic in isolation. It is designed to verify that one stored procedure, function, or trigger behaves correctly under controlled conditions.

Question 2

A CI/CD pipeline deploys a DACPAC to a test database and then executes scripts that verify stored procedures, triggers, and application workflows function together correctly. What type of testing is being performed?

A. Integration testing

B. Smoke testing

C. Performance testing

D. Security testing

Answer: A

Explanation:
Integration testing verifies that multiple components interact correctly. It ensures database objects and applications work together after deployment.

Question 3

What is the primary purpose of regression testing?

A. Measure concurrent user performance

B. Verify that previous functionality still works after changes

C. Test database backups

D. Validate database security permissions

Answer: B

Explanation:
Regression testing ensures that new changes do not introduce defects into existing functionality. It is an important safeguard before production deployments.

Question 4

Which characteristic is considered a best practice for unit tests?

A. They should depend on production data.

B. They should require manual execution.

C. They should test multiple independent business processes simultaneously.

D. They should be repeatable and deterministic.

Answer: D

Explanation:
Good unit tests should produce consistent results every time they run, be independent of external factors, and execute automatically.

Question 5

Why should automated tests be included in every CI/CD pipeline?

A. To eliminate the need for version control

B. To automatically replace database administrators

C. To detect defects early and prevent faulty deployments

D. To remove the need for production monitoring

Answer: C

Explanation:
Automated testing provides immediate feedback on code changes, identifies problems early, and helps prevent unsuccessful deployments.

Question 6

A team wants to verify that a newly deployed database is accessible, key tables exist, and critical stored procedures execute successfully before running more extensive tests. Which testing approach should they use?

A. Regression testing

B. Load testing

C. Smoke testing

D. Unit testing

Answer: C

Explanation:
Smoke testing performs a quick validation that essential functionality is operational before more comprehensive testing begins.

Question 7

Which testing type is specifically intended to measure database behavior under thousands of simultaneous users?

A. Unit testing

B. Load testing

C. Regression testing

D. Static code analysis

Answer: B

Explanation:
Load testing evaluates how well a database performs under expected or peak workloads, measuring metrics such as throughput, response time, and resource utilization.

Question 8

Which statement best describes a unit test?

A. It validates interactions between multiple applications.

B. It verifies production backup procedures.

C. It measures database performance under heavy workloads.

D. It tests a single database object independently.

Answer: D

Explanation:
Unit tests focus on individual components such as stored procedures, functions, or triggers, allowing developers to isolate and troubleshoot defects efficiently.

Question 9

A SQL Database Project build fails because an automated test detects incorrect results from a stored procedure. What should the CI/CD pipeline do?

A. Continue deployment to production.

B. Ignore the test because the build succeeded.

C. Disable future automated testing.

D. Stop the deployment until the issue is corrected.

Answer: D

Explanation:
One of the primary benefits of CI/CD is preventing defective code from reaching later environments. Failed automated tests should stop the deployment pipeline.

Question 10

Why is representative test data important when designing a database testing strategy?

A. It guarantees maximum query performance.

B. It helps ensure tests accurately reflect real-world scenarios while avoiding unnecessary production data exposure.

C. It automatically creates execution plans.

D. It eliminates the need for integration testing.

Answer: B

Explanation:
Representative test data enables realistic testing while reducing the risk of exposing sensitive production information. Well-designed test datasets improve the reliability and usefulness of automated tests.

Go to the DP-800 Exam Prep Hub main page

Data Development, Databases, DP-800, Microsoft Certification, SQL

Identify and resolve query performance issues, including blocking and deadlocks – Part 3 (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.

Best Practices for Preventing Performance Problems

The DP-800 exam emphasizes preventing problems rather than simply reacting to them.

Good database design, indexing, and application coding practices significantly reduce blocking, deadlocks, and poor query performance.

Design Tables Properly

Avoid:

  • excessively wide rows
  • unnecessary nullable columns
  • poor normalization
  • over-normalization requiring many joins

Good schema design leads to:

  • smaller pages
  • fewer logical reads
  • shorter lock durations

Use Appropriate Data Types

Poor choices increase memory usage.

Instead of:

NVARCHAR(MAX)

use

NVARCHAR(50)

when appropriate.

Benefits include:

  • reduced I/O
  • better index efficiency
  • improved cache utilization

Keep Transactions Short

One of the biggest causes of blocking is long-running transactions.

Bad:

BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
WAITFOR DELAY '00:05:00';
COMMIT;

Locks remain active for five minutes.

Better:

BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
COMMIT;

Commit Frequently

Instead of updating millions of rows in one transaction:

UPDATE LargeTable
SET Status = 'Complete';

process smaller batches.

Example:

WHILE 1=1
BEGIN
UPDATE TOP (1000) LargeTable
SET Status='Complete'
WHERE Status='Pending';
IF @@ROWCOUNT=0
BREAK;
END

Benefits:

  • shorter locks
  • reduced log growth
  • less blocking

Create Effective Indexes

Missing indexes often lead to:

  • table scans
  • excessive logical reads
  • blocking
  • CPU spikes

Create indexes on:

  • frequently filtered columns
  • join columns
  • ORDER BY columns

Example:

CREATE INDEX IX_OrderDate
ON Sales(OrderDate);

Avoid Too Many Indexes

Indexes improve reads.

Indexes slow:

  • INSERT
  • UPDATE
  • DELETE

Every modification updates every affected index.

Balance read performance against write performance.

Maintain Indexes

Over time indexes fragment.

Use:

ALTER INDEX ALL
ON Sales
REBUILD;

or

ALTER INDEX ALL
ON Sales
REORGANIZE;

Generally:

  • REORGANIZE for moderate fragmentation
  • REBUILD for heavy fragmentation

Write Efficient Queries

Avoid:

SELECT *

Use:

SELECT CustomerID,
       CustomerName

Benefits:

  • less network traffic
  • narrower execution plans
  • smaller memory grants

Filter Early

Instead of processing entire tables:

SELECT *
FROM Sales;

Use:

SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01';

Avoid Functions on Indexed Columns

Bad:

WHERE YEAR(OrderDate)=2025

This prevents index seeks.

Better:

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

Use EXISTS Instead of IN When Appropriate

Example:

WHERE EXISTS
(
SELECT *
FROM Orders
WHERE Orders.CustomerID=Customers.CustomerID
)

Often performs better on large datasets.

Parameter Sniffing

Parameter sniffing occurs when SQL Server optimizes a stored procedure using the first parameter values it receives.

Example:

EXEC GetOrders 1;

The plan is cached.

Later:

EXEC GetOrders 100000;

The same plan may perform poorly.

Possible solutions:

  • OPTION (RECOMPILE)
  • OPTIMIZE FOR
  • local variables
  • Query Store plan forcing

Monitor Wait Statistics

Wait statistics reveal what SQL Server spends time waiting on.

Common waits include:

Wait TypeMeaning
PAGEIOLATCHWaiting for disk I/O
CXPACKETParallelism
LCK_M_XExclusive lock
LCK_M_SShared lock
WRITELOGLog write bottleneck
SOS_SCHEDULER_YIELDCPU pressure

Query:

SELECT *
FROM sys.dm_os_wait_stats;

Wait statistics help identify the true bottleneck before making changes.

Monitor Resource Usage

Useful DMVs include:

CPU:

sys.dm_exec_query_stats

Memory:

sys.dm_os_memory_clerks

Locks:

sys.dm_tran_locks

Sessions:

sys.dm_exec_sessions

Requests:

sys.dm_exec_requests

Query Store Best Practices

Enable Query Store on production databases.

Benefits:

  • captures historical plans
  • tracks regressions
  • compares runtime statistics
  • forces known good plans

Avoid disabling Query Store unless troubleshooting specific issues.

Azure SQL Automatic Performance Features

Azure SQL Database provides automatic tuning.

Features include:

  • Automatic index creation
  • Automatic index removal
  • Automatic plan correction
  • Automatic plan regression detection

These features reduce administrative effort.

Common DP-800 Exam Tips

Know the differences between:

TopicKey Point
BlockingWaiting for locks
DeadlockCircular blocking; one transaction is terminated
Query StoreHistorical performance monitoring
DMVsReal-time diagnostic information
Execution PlansExplain how SQL executes queries
Missing Index DMVsRecommend useful indexes
Automatic TuningAzure SQL self-optimization
Snapshot IsolationReduces reader/writer blocking
Extended EventsModern tracing tool
Parameter SniffingCached plans may not fit all parameters

Summary

To excel in the DP-800 exam, you should be able to:

  • Interpret execution plans and identify expensive operators.
  • Use Query Store to identify regressions and force stable plans.
  • Query DMVs to diagnose slow-running queries, blocking, waits, and resource consumption.
  • Recognize and resolve blocking by shortening transactions, adding indexes, or using appropriate isolation levels.
  • Detect deadlocks with Extended Events, deadlock graphs, and system health sessions.
  • Understand common wait types and how they relate to CPU, I/O, memory, and locking issues.
  • Apply indexing, statistics maintenance, and efficient query-writing techniques to prevent performance problems.
  • Explain how Azure SQL automatic tuning can improve query performance and reduce administrative overhead.
  • Identify parameter sniffing scenarios and select appropriate mitigation strategies.

Practice Exam Questions

Question 1

A stored procedure performs well for some parameter values but poorly for others because SQL Server reuses a cached execution plan. Which performance issue is occurring?

A. Lock escalation

B. Parameter sniffing

C. Deadlocking

D. Page compression

Answer: B

Explanation:
Parameter sniffing occurs when SQL Server generates and caches an execution plan based on the first parameter values used. Subsequent executions with significantly different parameter values may reuse an inefficient plan, resulting in poor performance.

Question 2

A database administrator wants to reduce blocking caused by long-running UPDATE statements that affect millions of rows. Which approach is most effective?

A. Increase the database compatibility level

B. Disable Query Store

C. Process updates in smaller batches and commit frequently

D. Force all queries to use parallel execution

Answer: C

Explanation:
Breaking large modifications into smaller batches shortens transaction duration, releases locks more quickly, reduces transaction log growth, and minimizes blocking for other sessions.

Question 3

Which query is more likely to prevent SQL Server from performing an index seek on an indexed OrderDate column?

A.

WHERE OrderDate >= '2025-01-01'

B.

WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31'

C.

WHERE YEAR(OrderDate) = 2025

D.

WHERE OrderDate < '2026-01-01'

Answer: C

Explanation:
Applying a function such as YEAR() to an indexed column makes the predicate non-SARGable, often preventing SQL Server from using an index seek and forcing an index or table scan instead.

Question 4

Which DMV provides information about current lock resources held by transactions?

A. sys.dm_exec_query_stats

B. sys.dm_os_wait_stats

C. sys.dm_exec_sessions

D. sys.dm_tran_locks

Answer: D

Explanation:
sys.dm_tran_locks displays active lock information, including lock types, resources, and owning sessions, making it valuable when investigating blocking.

Question 5

Why should developers avoid using SELECT * in production queries whenever possible?

A. It always causes deadlocks.

B. It automatically disables indexes.

C. It retrieves unnecessary columns, increasing I/O and network traffic.

D. It prevents Query Store from capturing execution statistics.

Answer: C

Explanation:
Selecting only the required columns reduces disk reads, network traffic, memory usage, and execution costs while allowing SQL Server to generate more efficient execution plans.

Question 6

A SQL Server database contains heavily fragmented indexes after months of frequent updates. Which maintenance task should typically be performed when fragmentation is high?

A. Update statistics only

B. Rebuild the indexes

C. Shrink the database

D. Clear the plan cache

Answer: B

Explanation:
An index rebuild recreates the index structure, removes fragmentation, and updates index statistics. It is generally recommended when fragmentation is significant.

Question 7

A developer notices frequent LCK_M_X waits in SQL Server. What do these waits indicate?

A. CPU saturation

B. Memory allocation failures

C. Sessions waiting for exclusive locks

D. Network latency

Answer: C

Explanation:
LCK_M_X wait types indicate sessions waiting to acquire exclusive locks that are currently held by other transactions, suggesting blocking.

Question 8

Which Azure SQL feature can automatically detect a query plan regression and restore a previously better-performing execution plan?

A. Intelligent Insights

B. Automatic Plan Correction

C. Azure Monitor Alerts

D. Elastic Jobs

Answer: B

Explanation:
Automatic Plan Correction, part of Azure SQL automatic tuning, identifies query regressions and can force a previously efficient execution plan automatically.

Question 9

Which practice best helps prevent blocking in high-concurrency OLTP systems?

A. Keep transactions as short as possible.

B. Disable indexes during business hours.

C. Increase page size.

D. Use SELECT * in all reporting queries.

Answer: A

Explanation:
Short transactions reduce the amount of time locks are held, allowing other sessions to access data sooner and minimizing blocking.

Question 10

A DBA wants to determine whether SQL Server is primarily waiting on disk I/O, locking, or CPU scheduling before making performance changes. Which diagnostic information should be examined first?

A. Database file sizes

B. Transaction log backup history

C. Wait statistics

D. Server collation settings

Answer: C

Explanation:
Wait statistics provide a high-level overview of where SQL Server spends its time waiting, making them one of the best starting points for diagnosing performance bottlenecks before making tuning decisions.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification, SQL

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

In Part 1, we discussed locking, blocking, deadlocks, transaction isolation levels, and concurrency controls. In this section, we will focus on the tools and techniques that SQL developers use to diagnose and resolve performance issues. These tools are frequently referenced throughout Microsoft documentation and are highly relevant for the DP-800 certification exam.

After completing this article, you should be able to:

  • Interpret query execution plans.
  • Use Query Store to analyze historical query performance.
  • Leverage Dynamic Management Views (DMVs) to monitor database activity.
  • Capture performance issues with Extended Events.
  • Interpret wait statistics.
  • Optimize indexes.
  • Address parameter sniffing issues.
  • Maintain statistics.
  • Follow a structured performance tuning methodology.
  • Recognize common DP-800 exam scenarios.

A Structured Performance Tuning Process

Performance tuning should follow a systematic approach rather than relying on guesswork.

A recommended workflow is:

  1. Identify the slow query.
  2. Capture the execution plan.
  3. Examine wait statistics.
  4. Check index usage.
  5. Review Query Store history.
  6. Examine DMVs.
  7. Optimize the query or indexes.
  8. Test the improvement.
  9. Monitor ongoing performance.

Following a structured process helps avoid unnecessary changes that may introduce new problems.

Understanding Query Execution Plans

An execution plan is a roadmap that shows how SQL Server processes a query.

It displays:

  • Order of operations
  • Index usage
  • Join methods
  • Estimated and actual row counts
  • Operator costs
  • Memory grants
  • Parallelism decisions

Execution plans help identify why a query is slow.

Estimated vs. Actual Execution Plans

Estimated Execution Plan

Generated before execution.

Advantages:

  • No query execution required
  • Useful during development
  • Quick to generate

Limitations:

  • Uses estimated statistics
  • Does not show runtime behavior

Actual Execution Plan

Generated while the query executes.

Advantages:

  • Shows actual row counts
  • Displays actual execution times
  • More accurate for troubleshooting

Requires executing the query.

Reading Execution Plans

Several operators frequently appear in execution plans.

Index Seek

The optimizer directly locates matching rows.

Characteristics:

  • Fast
  • Efficient
  • Low I/O
  • Preferred operation

Index Scan

Reads most or all index pages.

May be acceptable when:

  • Returning many rows
  • Small tables

May indicate missing indexes if unexpected.

Table Scan

Reads the entire table.

Usually indicates:

  • Missing indexes
  • Poor filtering
  • Small tables

Large table scans often create significant I/O.

Nested Loop Join

Efficient when one input is small.

Ideal for:

  • Primary key lookups
  • Highly selective joins

Merge Join

Efficient when both inputs are sorted.

Often used with:

  • Clustered indexes
  • Ordered datasets

Hash Match

Builds hash tables.

Common for:

  • Large joins
  • Large aggregations

Requires considerable memory.

Cost Percentages

Execution plans assign estimated costs.

Example:

OperatorCost
Index Seek5%
Nested Loop10%
Sort35%
Hash Match50%

These percentages are estimates, not actual elapsed time.

Focus on expensive operators as starting points for optimization.

Warning Indicators

Execution plans may include warnings such as:

  • Missing indexes
  • Implicit conversions
  • Hash spills
  • Sort spills
  • Excessive memory grants
  • Parallelism skew

Warnings deserve investigation but should not automatically be implemented without testing.

Query Store

Query Store records query history over time.

It stores:

  • Query text
  • Execution plans
  • Runtime statistics
  • Resource consumption
  • Plan history
  • Wait statistics (supported versions)

Unlike the plan cache, Query Store persists across restarts.

Benefits of Query Store

Query Store enables developers to:

  • Identify regressed queries
  • Compare historical execution plans
  • Detect parameter-sensitive plan changes
  • Force a known good execution plan
  • Analyze workload trends

Query Store is one of the most valuable performance troubleshooting features available in SQL Server and Azure SQL.

Common Query Store Reports

Useful reports include:

  • Top Resource Consuming Queries
  • Queries with High Duration
  • Queries with High CPU
  • Query Wait Statistics
  • Regressed Queries
  • Plan Comparison

These reports quickly identify problematic queries.

Forcing Execution Plans

Occasionally, SQL Server chooses a poor plan.

Query Store allows administrators to force a previous stable plan.

Advantages:

  • Quick recovery
  • No code modification
  • Useful after upgrades
  • Helps address parameter-sensitive regressions

Forced plans should be monitored to ensure they remain optimal as data changes.

Dynamic Management Views (DMVs)

DMVs provide real-time information about SQL Server activity.

They are essential for performance troubleshooting.

Examples include:

  • Active requests
  • Sessions
  • Index usage
  • Missing indexes
  • Wait statistics
  • Cached execution plans
  • Memory usage

Frequently Used DMVs

sys.dm_exec_requests

Displays currently executing requests.

Useful columns include:

  • session_id
  • status
  • wait_type
  • blocking_session_id
  • cpu_time
  • logical_reads

Example:

SELECT
    session_id,
    status,
    cpu_time,
    logical_reads,
    blocking_session_id
FROM sys.dm_exec_requests;

sys.dm_exec_sessions

Displays connected sessions.

Useful for identifying:

  • Login information
  • Application names
  • Client connections
  • Session status

sys.dm_exec_query_stats

Provides cumulative statistics.

Includes:

  • Execution count
  • CPU usage
  • Logical reads
  • Elapsed time

Excellent for identifying expensive queries.

sys.dm_db_index_usage_stats

Shows index usage.

Useful for identifying:

  • Unused indexes
  • Frequently used indexes
  • Missing optimization opportunities

sys.dm_db_missing_index_details

Recommends potential indexes.

Important:

These recommendations should always be evaluated carefully rather than implemented automatically.

Extended Events

Extended Events is SQL Server’s modern monitoring framework.

It replaces SQL Trace and SQL Server Profiler for most workloads.

Advantages:

  • Lightweight
  • Highly configurable
  • Lower overhead
  • Suitable for production environments

Common Extended Event Sessions

Extended Events can capture:

  • Deadlocks
  • Blocking
  • Long-running queries
  • Login failures
  • Wait statistics
  • Query execution
  • Memory grants

The built-in system_health session captures many important diagnostic events by default.

Wait Statistics

Wait statistics show where SQL Server spends time waiting.

Rather than measuring CPU usage alone, waits reveal resource bottlenecks.

Common categories include:

  • CPU
  • Disk I/O
  • Memory
  • Locks
  • Network
  • Parallelism

Common Wait Types

LCK_M_*

Lock waits.

Indicate blocking.

Possible causes:

  • Long transactions
  • Lock contention
  • Missing indexes

PAGEIOLATCH_*

Waiting for data pages from disk.

May indicate:

  • Slow storage
  • Large scans
  • Insufficient memory

CXPACKET / CXCONSUMER

Related to parallel query execution.

May indicate:

  • Large parallel queries
  • Uneven workload distribution

Not always a problem.

WRITELOG

Waiting for transaction log writes.

May indicate:

  • Heavy write activity
  • Slow storage subsystem

SOS_SCHEDULER_YIELD

CPU scheduling wait.

May indicate CPU pressure.

Index Optimization

Indexes greatly influence performance.

Well-designed indexes reduce:

  • Logical reads
  • CPU usage
  • Query duration
  • Blocking

Poor indexes increase maintenance costs.

Clustered vs. Nonclustered Indexes

Clustered Index

  • Determines physical row order.
  • One per table.
  • Ideal for range queries.

Nonclustered Index

  • Separate structure.
  • Many allowed.
  • Ideal for selective lookups.

Covering Indexes

A covering index contains all columns required by a query.

Benefits include:

  • Eliminates key lookups
  • Reduces logical reads
  • Improves performance

Example:

CREATE INDEX IX_Orders_Customer
ON Sales.Orders(CustomerID)
INCLUDE(OrderDate, TotalAmount);

Index Fragmentation

Fragmented indexes reduce performance.

Maintenance options include:

FragmentationRecommended Action
Less than 5%No action
5–30%Reorganize
Greater than 30%Rebuild

Regular maintenance improves read performance.

Parameter Sniffing

SQL Server caches execution plans.

Sometimes the first parameter value generates a plan that performs poorly for later executions.

Example:

A plan optimized for one customer with only a few orders may perform poorly when reused for a customer with millions of orders.

Potential mitigation techniques include:

  • OPTION (RECOMPILE)
  • OPTIMIZE FOR
  • Local variables (used judiciously)
  • Query Store plan forcing
  • Query redesign

Understanding parameter sniffing is an important DP-800 objective.

Statistics Maintenance

Statistics help SQL Server estimate row counts.

Outdated statistics lead to:

  • Poor cardinality estimates
  • Incorrect join selection
  • Poor execution plans

Maintenance options include:

UPDATE STATISTICS Sales.Orders;

or

EXEC sp_updatestats;

Automatic statistics updates are generally sufficient for many workloads, but large or highly volatile databases may benefit from scheduled maintenance.

Intelligent Performance Features

Modern SQL Server and Azure SQL include intelligent features such as:

  • Automatic tuning
  • Automatic plan correction
  • Automatic index recommendations
  • Intelligent Insights (Azure SQL)
  • Automatic statistics updates

These features assist administrators but should complement—not replace—performance analysis and testing.

Common Performance Optimization Techniques

When troubleshooting slow queries:

  • Retrieve only required columns.
  • Avoid SELECT *.
  • Use appropriate indexes.
  • Write SARGable predicates.
  • Keep transactions short.
  • Avoid cursors when set-based operations are possible.
  • Maintain indexes and statistics.
  • Reduce unnecessary sorting.
  • Limit large result sets.
  • Batch large modifications.

Performance Troubleshooting Checklist

When investigating a slow query:

☐ Is an appropriate index available?

☐ Is SQL Server performing an Index Seek or Table Scan?

☐ Are statistics current?

☐ Are implicit conversions occurring?

☐ Is blocking present?

☐ Is parameter sniffing affecting performance?

☐ Are waits indicating CPU, I/O, or locking problems?

☐ Does Query Store show a regression?

☐ Can the query be rewritten more efficiently?

☐ Has the improvement been tested before deployment?

Real-World Scenario 1: Missing Index

A customer search query takes 18 seconds.

Execution plan shows:

  • Table Scan
  • Missing Index recommendation

Resolution:

Create an appropriate nonclustered index and validate the improvement with the actual execution plan.

Real-World Scenario 2: Parameter-Sensitive Plan

A stored procedure runs quickly for most customers but very slowly for one large customer.

Investigation shows a cached plan optimized for a small data set.

Resolution:

Evaluate parameter-sensitive plan optimization techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, Query Store plan forcing, or redesigning the query, depending on the workload.

Real-World Scenario 3: Blocking Chain

Users report intermittent timeouts.

DMVs reveal:

Session 52 blocks Session 63.

Session 63 blocks Session 81.

Session 81 blocks Session 96.

Root cause:

A long-running transaction remained open while waiting for application logic.

Resolution:

Reduce transaction duration and ensure commits occur as quickly as possible.

DP-800 Exam Tips

  • Understand the differences between Estimated and Actual execution plans.
  • Know when Index Seeks are preferred over Table Scans.
  • Be familiar with Query Store, including plan history, runtime statistics, and plan forcing.
  • Recognize the most commonly used DMVs for monitoring active requests, sessions, query performance, and index usage.
  • Understand how Extended Events have largely replaced SQL Trace and SQL Server Profiler for production monitoring.
  • Learn to interpret common wait types such as LCK_M_*, PAGEIOLATCH_*, CXPACKET, WRITELOG, and SOS_SCHEDULER_YIELD.
  • Understand the role of statistics, parameter sniffing, covering indexes, and fragmentation in query performance.
  • Remember that Microsoft recommends making tuning decisions based on evidence from execution plans and monitoring tools, rather than assumptions.

Key Takeaways

Performance tuning is an iterative process that combines analysis, measurement, and optimization. SQL Server provides a rich set of diagnostic tools—including execution plans, Query Store, DMVs, Extended Events, wait statistics, and index analysis—that help developers identify and resolve bottlenecks. For the DP-800 exam, you should be comfortable selecting the appropriate diagnostic tool, interpreting its results, and recommending effective solutions to improve query performance while maintaining scalability and concurrency.

Go to the DP-800 Exam Prep Hub main page

Data Development, DP-800, Microsoft Certification, SQL

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:

  • IS
  • IX
  • SIX

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

BlockingDeadlock
Temporary waitingCircular waiting
Usually resolves automaticallyRequires SQL Server intervention
No transaction rollbackOne transaction rolled back
Normal behaviorUndesirable behavior
Caused by incompatible locksCaused 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 LevelDirty ReadsBlockingConcurrency
READ UNCOMMITTEDYesVery LowVery High
READ COMMITTEDNoModerateGood
REPEATABLE READNoHigherModerate
SERIALIZABLENoHighestLowest
SNAPSHOTNoLowExcellent

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

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:

  1. Parse the T-SQL statement
  2. Validate syntax and object names
  3. Optimize the query
  4. Generate an execution plan
  5. 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:

OperatorPurpose
Table ScanReads every row in a table
Clustered Index ScanScans an entire clustered index
Index SeekEfficiently locates matching rows
Key LookupRetrieves additional columns from a clustered index
Nested LoopsEfficient join for small result sets
Merge JoinEfficient for sorted data
Hash MatchEfficient for large unsorted datasets
SortOrders rows
Compute ScalarCalculates expressions
FilterApplies 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:

  1. Identify a slow query.
  2. Capture the actual execution plan.
  3. Review Query Store history.
  4. Check DMVs for CPU, I/O, and wait statistics.
  5. Identify inefficient operators.
  6. Evaluate indexing opportunities.
  7. Update statistics if needed.
  8. Test improvements.
  9. 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