What is view?
Basically view is virtual table and basically it used for
- To provide a security mechanism that restricts users to a certain subset of data in one or more base tables.
- To provide a mechanism that allows developers to customize how users can logically view the data stored in base tables.
What is Indexed View?
Indexed view is the clustered index
that is created on view.
How to create view ?
To create view you need to use below statement
CREATE VIEW [VIEW NAME]
AS
SELECT ID1,ID2 FROM TABLE
How to create Index on View ?
CREATE UNIQUE CLUSTERED
INDEX
[IX_VIEW] ON [VIEWNAME]
(
[COLUMN NAME] [ASC/DSC]
)
Which index used when?
1)
Now suppose you have created one Index on
empTable.EMPID and then you create view for this
2)
Now you have also create one cluster index for
view on empTable.ID
Now Questions are....
Q1. Does the table use an index
created on itself?
Q2. Does the view use an index created on itself?
Q3. Do both the queries use the same index? If yes, why? If no, why not?
Q2. Does the view use an index created on itself?
Q3. Do both the queries use the same index? If yes, why? If no, why not?
Answer is very clear
Answer 1 :
When you select the record in table it will use IX_TABLE
Answer 2
When you select the record in view it will use IX_TABLE
Answer 3
It will cost more time to refer view index (in simple query)
so sql server use table index.
How can I force fully call Index View ?
By using NOEXPAND key word you can force the sql server to
use Index view
SELECT
Column1, Column2, ... FROMView1 WITH (NOEXPAND) WHERE
Share your feedback and if you have any question send me an email on viralpala@gmail.com