As you probably are already aware, a view in SQL Server is generated off of a pre-defined select statement, and is a flexible solution to use when ever you are doing database driven application development; particularly when you are using an ORM tool (like SubSonic or EntitySpaces as examples). However, for performance reasons there is an additional piece you should be aware of and this is what is known as an "indexed view".
On the surface, an indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it, effectively working around the "one clustered index per table" limitation. The downside to this is that the indexed view is a full copy of the data from the underlying tables, so for space considerations you will need to take this into account when using them.
Let's look first at how to create a normal view, so that we have something to compare to as we create an indexed view
create view ContactsView as
select [Users].[FirstName], [Users].[LastName], [Contacts].*
inner join [Contacts] on [Contacts].[UserID] = [Users].[UserId]
What this will do is create a pretty flexible view that will only pull the first and last name of the user from the users table, and all of the columns from the Contacts table and automatically pick up any new columns that are added to the Contacts table, with no further modifications. Indexed views require a more rigid definition of what they will return. For this reason, we cannot use a wildcard (*) in the underlying query. Each column must be individually called out; also, each table named in the query must be in 2 part dot notation, referencing both the schema of the table and the table name itself, and the view must be declared with schemabinding enabled for it.
Below is the modified query to create a view capable of being an indexed view:
create view [dbo].[OrganizationContactsView] with schemabinding as
select [FirstName], [LastName], [Contacts].[UserID], [Contacts].[ContactDescription], [Contacts].[IsPublic]
inner join [dbo].[Users] on [Contacts].[UserID] = [Users].[UserId]
Now, we need to create the clustered index on it. Clustered indexes on a view must be unique, otherwise you will not be able to create one; nonclustered indexes will work fine. For the present example, a UserId will only be in the contacts table once per Organization, so we can make a unique clustered index spanning the OrgId and UserId columns, like this:
create unique clustered index IX_OrganizationContactsView_OrgId on [OrganizationContactsView](OrgId,UserId)
And, there we are, a clustered index!
Nov 26 2008, 04:33 PM