?
Solved

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

Posted on 2007-12-06
10
Medium Priority
?
1,620 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
8 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

The Growing Need for Data Analysts

As the amount of data rapidly increases in our world, so does the need for qualified data analysts. WGU's MS in Data Analytics and maximize your leadership opportunities as a data engineer, business analyst, information research scientist, and more.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

579 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