System Design Considerations - Pro SQL Server Internals - page 349

Views allow you to perform some level of database schema refactoring transparently to client applications.
For example, you can normalize or denormalize tables and hide the changes by adding or removing joins in the view.
Those changes would be transparent to client applications as long as the view interface remains intact, and the view is
still updateable if needed.
As you already know, this flexibility comes at a cost. Accessing data through views can introduce unnecessary
joins, which contributes to SQL Server load. You should create underlying foreign key constraints and allow SQL
Server to eliminate unnecessary joins when working with views.

■ SQL Server does not perform join elimination in the case of composite foreign key constraints. As a workaround,
you can define views with outer joins when possible.

Tip

Using views for the sole purpose of abstracting database schema from client applications is not very beneficial
due to the potential performance issues that views introduce. However, views can help if security is a concern. You
should consider combining them with other techniques when modifying the data, however. As an example, you can
use views to read the data using stored procedures for data modifications.

Stored Procedures
The approach of using stored procedures for data access has been historically favored by a large number of database
professionals. It completely isolates client applications from the data, and it allows easy and transparent database
schema and code refactoring during development and performance tuning. Similar to database views, stored
procedures can provide an additional layer of security; you can grant users the ability to execute stored procedures
without giving them access to the underlying tables.
Stored procedures reduce the load on SQL Server by reusing execution plans, which are typically cached. They
can also solve the problem of inefficient execution plans due to implicit conversions when parameters and column
data types do not match.
Listing 16-2 illustrates an example of this. As you know, nvarchar is not a SARGable predicate for the indexes
on the varchar columns. It is very common for client applications to treat strings as unicode and generate nvarchar
parameters when the parameter type has not been explicitly specified. Two calls of the sp_executesql procedure in
the listing show typical calls from the ADO.Net client library.

Listing 16-2. Implicit data type conversion in the case of stored procedures and dynamic SQL

create table dbo.Customers
(
CustomerId int not null,
CustomerName varchar(64) not null,
PlaceHolder char(100),
constraint PK_Customers
primary key clustered(CustomerId)
);