SQL View & Indexed View

 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?

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