Views - Pro SQL Server Internals - page 183

■ Using tempdb as the staging area can improve the performance of the processing due to more efficient
transaction logging there, although it introduces development challenges because sQL server recreates tempdb on
restart. we will talk more about tempdb in Chapter 12, “temporary tables” and discuss tempdb transaction logging in
Chapter 29, “transaction Log internals.”

Tip

Indexed Views
As opposed to regular views, which are just metadata, indexed views materialize the data from the view queries storing
it in the database similarly to the tables. Then every time the base tables are updated, SQL Server synchronously
refreshes the data in the indexed views, thus keeping them up to date.
In order to define an indexed view, you need to create a regular view using schemabinging option. This option
binds the view and underlying tables, and it prevents any alteration of the tables that affects the view.
Next, you need to create a unique clustered index on the view. At this point, SQL Server materializes the view data
in the database. You can also create non-clustered indexes if needed after the clustered index has been created. When
indexes are defined as unique, SQL Server enforces the rule and fails the modifications of the base tables in case of
a uniqueness violation.
One of the common use-cases for indexed views prior to SQL Server 2008 was for supporting uniqueness
on a subset of values. We discussed one such example, uniqueness of the optional SSN column , in Chapter 4,
“Special Features,” solving the problem by creating a filtered index on the SSN column.
Listing 9-14 shows how you can use indexed view to achieve the same results.

Listing 9-14. Enforcing uniqueness of not-null values in nullable column with indexed view

create table dbo.Clients
(
ClientId int not null,
Name nvarchar(128) not null,
SSN varchar(10) null
)
go

create view dbo.vClientsUniqueSSN(SSN)
with schemabinding
as
select SSN from dbo.Clients where SSN is not null
go
create unique clustered index IDX_vClientsUniqueSSN_SSN on dbo.vClientsUniqueSSN(SSN)

At that point, you would not be able to insert a non-unique SSN value into the table because it violates the
uniqueness of the clustered index of the view.
There are plenty of requirements and restrictions in order for a view to be indexable. To name just a few, a view
cannot have subqueries, semi and outer joins, reference LOB columns, and have UNION , DISTINCT, and TOP specified.
There are also the restrictions on the aggregate functions that can be used with a view. Finally, a view needs to be
created with specific SET options, and it can reference only deterministic functions.