?
Solved

What are "Indexed Views" ? Need simple example on "Indexed View" - SQL Server 2005 / 2008 !!

Posted on 2007-12-06
10
Medium Priority
?
1,603 Views
Last Modified: 2008-09-20
Hi,

Can you please let me know:

1. What are "Indexed Views" ?
2. Why they are used ?
3. Need simple example on this.

Here is the base table i am having in SQL Server 2005 / 2008:

USE Sample
GO

-- Create a table

CREATE TABLE dbo.T
(
         X          int,
         Y          int
)
GO

-- Insert some sample data

INSERT INTO T VALUES (1, 2)
GO

INSERT INTO T VALUES (2, 3)
GO

INSERT INTO T VALUES (3, 4)
GO

-- Select the contents

SELECT * FROM T
GO

/*

   Create an Indexed View here

*/

If you want to change the table structure, please go ahead and change according to your need. Please keep the example so simple to understand the basic concept. Appreciate your time and patience !!

Thanks

Open in new window

0
Comment
Question by:kishore_peddi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20424626
3. This will create an indexed view

create view vT with schemabinding as
select X,Y from T
create unique clustered index vTy on vT(Y)

1. Indexed views are used to COPY all the data in the table, but ARRANGE (cluster) them differently

2. This is so that you have an alternate way to access them, which will be faster for certain cases
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20424646
Imagine your books in the library are filed by rows, by category, i.e. Science, Fiction, Magazines etc.
This is equivalent to the storage for your table - ordered a particular way.

Normal indexes are cards that tell you where to look for an item.  So you go to the card index, which is sorted by name, i.e author J for JKRowlings. This wells you which row to go to to find your book.  To find 3 books, you need to find 3 cards then go to the rows.

Consider indexed views the equivalent of photocoying ALL the books, and creating another room with all the books by rows, arranged by Author (regardless of category).  Now if you need a book by three authors, all you have to do is go directly to the row for J, K, T and find the books there.

For that reason, indexed views can cause your storage size to balloon (data is stored twice).
0
 

Author Comment

by:kishore_peddi
ID: 20424701
Hi imitchie,

Good explanation. But here are two things making me mad:

1. select * from vT with (noexpand)
2. select * from vT option (Expand Views)

What they are ? And can we create clustered index / non-clustered index on a view (If yes...then how) ?

Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:imitchie
ID: 20424753
You need to first create clustered indexes on view (thereby materialising them).
After that, because it's now a sort-of-data table, you can create other indexes.

These are all in books online:
EXPAND Views causes View indexes to be ignored. It acts like you never created a indexed view out of it, and uses the query inside the view.
NOExpand does the opposite.

Check books online for more info.
0
 
LVL 5

Expert Comment

by:ursangel
ID: 20426546
An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.
0
 
LVL 5

Expert Comment

by:ursangel
ID: 20426549
Check for the SQL 20005 Help or MSDN online
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20430556
To give follow up on ursangel:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Hope this helps ...
0
 
LVL 22

Expert Comment

by:dportas
ID: 20566698
In SQL Server 2005 and 2008 you can use INCLUDE on the CREATE INDEX statement to store additional columns in the leaf pages of a non-clustered index. In many cases this will achieve the same thing as an indexed view and has the advantage that all SQL Server editions can take best advantage of the index. Consider using the INCLUDE option instead of an indexed view where possible (does not apply to 2000).
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question