Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1628
  • Last Modified:

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

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
kishore_peddi
Asked:
kishore_peddi
1 Solution
 
imitchieCommented:
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
 
imitchieCommented:
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
 
kishore_peddiAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
imitchieCommented:
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
 
ursangelCommented:
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
 
ursangelCommented:
Check for the SQL 20005 Help or MSDN online
0
 
YveauCommented:
To give follow up on ursangel:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Hope this helps ...
0
 
dportasCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now