Jeffrey Williams (6/22/2008)
I am not sure where synonyms cannot be used that views can. Any examples?
Synonyms can not use Schema-binding nor be referenced by schema-bound objects. That means that if you decide to impose schema-binding on an application schema in the future, you will have to remove all of the synonyms. So that's one less option.
I like the ability to abstract out access to linked servers and/or other databases using synonyms. This allows for easier movement when needed.
Views do exactly the same thing, only with more options and more options for the future.
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.
This is no different from using views in your schema instead of synonyms. You just change a view defintion instead of changing a synonym defintion. No difference.
So, yeah - I think it is one step further because it adds additional options.
??? Huh? You still haven't named a single thing that a Synonym referencing tables & views can do that a View cannot do! You could replace "synonym" with "view" in everything that you said and it would still be true. How is that more options?
As for views, A) They can be schema-bound and be referenced by schema-bound objects, and B) besides acting as an unfiltered alias, as Synonyms do, Views can also:
1) control the column list returned
2) filter the rowset returned
3) collapse relational references to other tables
4) control which columns are writeable
5) elaborate hiearchies
etc., etc...
And these options are available now and anytime in the future, so more flexible, more options and useable in more situations.
Now I will grant you, that for all non-Table-valued objects that Synonyms work on they are the aliasing tool of choice, but for Views & Tables, I still cannot see one reason to prefer Synonyms over Views.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]