Comparing Views, Inline Functions, and Synonyms :: SQL Server 2012

As the lead database developer on a new project, you need to expose a logical view of the database to applications that produce daily reports. Your job is to prepare a report for the DBA team, showing the advantages and disadvantages of views, inline functions, and synonyms for creating that logical view of the database. What would you recommend using, based on each of the following conditions: views, inline functions, or synonyms?
  1. The application developers do not want to work with complex joins for their reports. For updating data, they will rely on stored procedures.

  2. In some cases, you need to be able to change the names of tables or views without having to recode the application.

  3. In other cases, the application needs to filter report data on the database by passing parameters, but the developers do not want to use stored procedures for retrieving the data.
Answers
  1. To remove the need for developers working with complex joins, you can present them with views and inline functions that hide the complexity of the joins. Because they will use stored procedures to update data, you do not need to ensure that the views are updatable.

  2. You can change the names or definitions of views and change table names without affecting the application if the application refers to synonyms. You will have to drop and recreate the synonym when the underlying table or view has a name change, and that will have to be done when the application is offline.

  3. You can use inline functions to provide viewlike objects that can be filtered by parameters. Stored procedures are not required because users can reference the inline function in the FROM clause of a query.