Should I Use Synonyms or Views
Published: 19th July 2015
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.
Comments:
NB: Comments will only appear once they have been moderated.