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

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:

Automatic File Discovery

One major benefit is automatic inclusion.

Suppose a developer creates:

Tables
Products.sql

The project automatically includes it.

No project modification is required.

This greatly reduces merge conflicts in Git.

Platform Targets

Projects target a SQL platform.

Examples include:

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

The selected platform determines which SQL features are valid.

For example:

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

Schema Validation

During the build, SQL Database Projects perform extensive validation.

Checks include:

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

This allows developers to catch issues long before deployment.

Dependency Analysis

The build engine understands dependencies.

For example:

View
↓
Table
↓
Schema

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

Object Dependency Example

Consider:

CREATE VIEW SalesSummary
AS
SELECT *
FROM Sales;

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

Compile-Time Validation vs Runtime Validation

Compile-time 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