Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-12-06
10
Medium Priority
?
1,615 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Loops Section Overview

610 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