Solved

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

Posted on 2007-12-06
10
1,561 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
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 500 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now