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:
- ·
A 3-part name is unnecessary (despite what SSMS
might spit out with “Script Table As > Select To”)
- Explicitly naming the database hardcodes the
database name in the definition of the object.
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: SSDT