I am not sure where synonyms cannot be used that views can. Any examples? I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.

One system I work with can be built to access a database locally or through a linked server. For instances where this database is remote, we have to run a script (provided by the vendor, of course) that modifies all views and stored procedures by adding in the reference to the linked server.

Let's say we have three systems (I have more than this). The DEV system has all databases local, the QA system has this one database on a linked server and live also has this database on a linked server.

The code on DEV uses 'database.schema.object' to reference the tables. The code in QA uses 'qalinkedserver.database.schema.object' and the code in live uses 'livelinkedserver.database.schema.object'.

In this scenario - any changes to the code in DEV cannot be moved to QA or even on to LIVE as is. It must be modified in each environment for that environments specific linked server.

Using schemas and synonyms makes the above a lot easier to manage because all code stays the same, regardless of whether or not the system is using a linked server.

Now I know we can create the linked servers the same on each environment - but that also has it's own problems with additional management.

The only place I see that views would work better is when you need a query that accesses multiple objects. In that case, you are either going to create the view in the source database and use a synonym to reference the view - or you are going to create the view in the local database and reference the view. If you reference the view locally - you have the option of accessing the objects through the linked server directly, or through the synonyms that you have created.

So, yeah - I think it is one step further because it adds additional options.

Jeff