Do you know how many orphaned objects are in your databases? Or references to columns, views, tables, or stored procedures that are long since removed or renamed and forgotten? Are you reluctant to change the name of a table or column because you don't know all the places it could be referenced?

Tracking the design changes of a SQL database over time and across multiple environments (dev, test, and prod) can be difficult. And if you have multiple team members who can make changes, then coordinating those changes is also challenging.

A database project in SQL Server Data Tools (SSDT, or Visual Studio) defines the schema of a database using the practice of "Desired State Configuration". This means that all the scripts in the database project are CREATE statements, and the scripts as a whole define the desired state of the database. Using this project source code, we can deploy to any target database and the deployment process will apply all the schema changes necessary to bring that target to the desired schema.

In this session I will show the important features of database projects and the development practices necessary to ensure that your deployments are as fast as possible and retain the existing data that may be present in the target database.
I'll show the value of the strong schema checking, refactoring support for object renaming, and how to implement default security, automated data patches, and support for referencing external objects, such as cross-database table references.
Finally I'll show how you can use a git repository in Azure DevOps and have code reviews and project compilation checks as part of a check-in operation.

If you use database projects in SSDT, you can be much more confident in the quality of your SQL code and in the reliability of your database deployments.