3-Part Naming in SSDT

2013-09-24
SSDT (the database tools one) enforces a 2-part naming convention when referencing objects within a project.  For example, a view might look something like this:

CREATE VIEW dbo.SomeView AS
    SELECT
          Customer_Id,
          Customer_Name
    FROM dbo.Customers

Note that the customers table above is referred to by [Schema].[Table].  When I start up a new SSDT project from an existing database, I often find a number of 3-part references sprinkled throughout the code.  That is, the view above would read:
    FROM SomeDatabase.dbo.Customers

In which case, SSDT will throw the following warnings:

SQL71562: View: [dbo].[SomeView] has an unresolved reference to object [SomeDatabase].[dbo].[SomeView].[Customer_Id]
SQL71562: View: [dbo].[SomeView] has an unresolved reference to object [SomeDatabase].[dbo].[SomeView].[Customer_Name]

Removing the reference to the database fixes these problems.  However, there are a couple good reasons why SSDT imposes a 2-part naming convention in the first place:

o   So?  This means that you cannot deploy working code to a database of a different name.
o   Why would I ever do that?  How about for parallel testing or branching?  Or for bringing the definition of production back to Dev and have it live side by side, without overlaying dev.  This is an excellent way for troubleshooting a production problem.

When you actually do need to reference an external database, this can be done using database references, which utilize SQLCMD variables for their name.  Similarly, the database name can be changed at the time of deployment to aid in testing and development.

Labels: