Strangenut

Creating an Indexed View in SQL Server 2005 and 2008

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].*
        from [Users]
       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]
        from [dbo].[Contacts]
       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!


Posted Nov 26 2008, 04:33 PM by dacrowlah

Comments

Tips for programmer wrote Tips for programmer
on 03-07-2012 5:29 AM

Pingback from  Tips for programmer

Add SQL Server view to ArcMap as XY event – Problem resolved « osedok wrote Add SQL Server view to ArcMap as XY event – Problem resolved « osedok
on 04-12-2012 7:43 AM

Pingback from  Add SQL Server view to ArcMap as XY event – Problem resolved « osedok

Powered by Community Server (Non-Commercial Edition), by Telligent Systems