ssdtDatabase References


Remarks

SQL Server includes a feature called delayed naming which means that in many cases you can deploy procedure and code that references objects that do not exist. It is also possible to drop or alter an object in such a way that any pieces of referencing code no longer run when called.

When you get either of theses two situations, you only know you have an issue when the code executes and fails.

SSDT helps with this by verifying that references to objects are valid when the project builds. This is one of the main benefits of SSDT and means that errors can be found at compile time rather than run time.

There are three types of Database Reference source:

  • Other projects in the same visual studio solution
  • Pre-built / supplied dacpacs for system databases (msdb and master)
  • Pre-built dacpacs for other databases / ssdt projects you create

Once you have a reference there are three different ways to use them in SSDT which map to the different ways we can reference objects in SQL Server:

  • Same Database
  • Different Database, Same Server
  • Different Database, Different Server

This allows us to use these names:

  • schema.table
  • database.schema.table
  • server.database.schema.table

This supports allowing:

  • Different projects to make one database
  • Cross database calls on the same server
  • Cross database calls via linked servers

Database references are key to getting SSDT up and running, understand the different ways that they can be used