0

Scenario: In SQL Server on-prem and plan to go the cloud in a year or two.

I am writing a stored procedure in database A and I need to access a table from database B.

Is it a best practice to create a synonym that points to the table in database B or just fully qualify the table in the package?

New contributor
aimyee is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • Please edit your question to specify what type of cloud service you plan to migrate to. Not all with support synonyms or cross-database access at all.
    – Paul White
    Jun 23 at 13:54
  • Cross-database references are generally problematic so I don't think there really is a best practice. And do not overlook how any dependent applications (and their development environments) support / interact with synonyms.
    – SMor
    Jun 23 at 13:56

2 Answers 2

0

There's a couple types of objects that I find make things more difficult to debug and / or manage in SQL Server, mostly because they end up being second thought to most developers. One of those things is Triggers and the other is Synonyms.

The reason I believe this for Synonyms is because:

  1. Synonyms are not commonly used as is, and many developers don't even know of their existence.

  2. Synonyms generally look (and function) like a regular entity object when part of a query, which can be confusing to other developers when they encounter one. When they try to trace back to where that Synonym object lives, it may take them a while to realize it's a Synonym and not an entity object like a Table or View for example. I'd say this is especially the case for developers who aren't leveraging schemas and keep everything in dbo.

Conversely, Synonyms are good in theory, especially for the use case of abstracting away the object reference that they are defined on. This way if such object is referenced in a lot of places, and one day needs to change, then you only need to update the Synonym definition. That's a change to only one object as opposed to having to fix the reference in all of the dependencies. So it can be argued Synonyms can improve database manageability too.

Additionally, having a naming convention with your Synonyms may help developers realize that it's a Synonym when they first encounter it, for example prefixing all synonyms with a lowercase s.


As for whether you should use one or not for your use cases depends on if you think the above good attributes of a Synonym outweigh the potential bad ones. There's not really a right or wrong answer here, it's up to you to decide. Personally I like to be explicit and fully qualify my references, as opposed to using Synonyms.

0

If the cloud solution you are using is SQL Azure, you're somewhat limited in what you can do, because cross-database queries run from database A would have to treat database B as an external datasource. Each table that you reference in database B would require that an external table be created in database A. I don't think making a synonym for each of those tables is going to help you get around that.

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql

Your Answer

aimyee is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.