This is a discussion I’ve had recently and it’s a bit of a weird one because there are actually very few times in which I would ever use a synonym, more often than not I would favour the view.
This is because in most circumstances they could actually be one and the same in terms of functionality and performance, but there are a couple of differences to consider.
Firstly, let’s create a simple example of each using a test database and referencing the sales.salesOrderHeader table in AdventureWorks2012:
use testDB
go
if object_id('salesOrderHeaderSyn') is not null drop synonym salesOrderHeaderSyn
if object_id('salesOrderHeaderView') is not null drop view salesOrderHeaderView
go
create synonym salesOrderHeaderSyn for AdventureWorks2012.sales.salesOrderHeader
go
create view salesOrderHeaderView
as
select *
from AdventureWorks2012.sales.salesOrderHeader
go
Accessing either of these objects is exactly the same as they simply provide a cleaner way to access tables in other databases or across linked servers:
select top 1 * from salesOrderHeaderSyn
select top 1 * from salesOrderHeaderView
So, as you can see, they’re pretty similar when used in a raw form like this. Therefore when would you likely use one over the other?
Well, in my opinion, the key points to consider are the following:
With a view you have the ability to include logic and therefore if you wish to only allow access to certain columns or record sets then you would need to use a view as a synonym is simply a “select *” with no other options available. For example:
create view salesOrderHeaderView
as
select *
from AdventureWorks2012.sales.salesOrderHeader
where SalesPersonID <= 100
go
You could also use a view in order to hide joins, case statements or any other business logic whilst making access simple for developers.
Therefore with this ability and with a basic “select *” also being catered for, I would generally opt for views in all cases.
However, there are times in which a synonym does, in fact, outweigh the view in terms of choice.
Let’s create a simple and small table and create both a synonym and view on top of it:
use testDB
go
if object_id('tempSales') is not null drop table tempSales
go
select SalesOrderID, SalesPersonID, SalesOrderNumber
into tempSales
from AdventureWorks2012.Sales.SalesOrderHeader
go
if object_id('salesOrderHeaderSyn') is not null drop synonym salesOrderHeaderSyn
if object_id('salesOrderHeaderView') is not null drop view salesOrderHeaderView
go
create synonym salesOrderHeaderSyn for tempSales
go
create view salesOrderHeaderView
as
select *
from tempSales
go
select top 1 * from salesOrderHeaderSyn
select top 1 * from salesOrderHeaderView
Now look what happens when we make a change to the table:
alter table tempSales add newColumn varchar(100)
Now let’s run our select statements:
select top 1 * from salesOrderHeaderSyn
select top 1 * from salesOrderHeaderView
So what happened? We even had “select *” in our view and therefore surely it should have picked up the new column?
Well no… the synonym is a simple pass through to the object itself and therefore whenever we change the object the synonym will automatically update, but the view doesn’t work in the same way. In order for us to get the right result we need to refresh it:
sp_refreshView 'salesOrderHeaderView'
select top 1 * from salesOrderHeaderView
Therefore if you are going to be changing any of your underlying data structures then I would much rather have synonyms pointing through to the objects than to have them all as views.
So if you are trying to decide which to use then the above are the main things I would consider first. Otherwise there’s little between them but, personally, I would tend to lean towards the view.