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?
- The application developers do not want to work with complex joins for their reports.
For updating data, they will rely on stored procedures.
- In some cases, you need to be able to change the names of tables or views without
having to recode the application.
- 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- 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.
- 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.
- 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.