index on view

Question

index on view

anil_kumar32

Ten Centuries

Points: 1378
More actions
July 8, 2011 at 6:50 am

#243948

[font="Times New Roman"]
Hi,
If i want to create a index on a view then why it is mandatory to create clustered index first?
Regards,
Anil K
[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a view is just a stored SQL statement. it gets run or rerun on demand whenever you select from it.

if you think about how indexes work, you can't put an index on a SQL statement itself, until you put a clustered index on it; (you might remember, the clustered index is actual data stored in a physical order )when that happens, the data is actually stored in memory pages...it's truely materialized just like a real table.

the SQL Engine then updates that views data pages whenever the underlying table.

once you have that clustered index in place, other, normal indexes can be additionally created, which all eventually use the clustered index to find the data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shazme Ten Centuries Points: 1292 More actions · Answer 2

Agree with Lowell.

The following steps required to create an indexed view are critical to the successful implementation of the view:

Verify the setting of ANSI_NULLS is correct for all existing tables that will be referenced in the view.

Verify ANSI_NULLS is set correctly for the current session as shown in the table in “Using SET Options to Obtain Consistent Results” before creating any new tables.

Verify ANSI_NULLS and QUOTED_IDENTIFIER are set correctly for the current session as shown in the table in “Using SET Options to Obtain Consistent Results” before creating the view.

Verify the view definition is deterministic.

Create the view using the WITH SCHEMABINDING option.

Verify your session's SET options are set correctly as shown in the table in “Using SET Options to Obtain Consistent Results” before creating the unique clustered index on the view.

Create the unique clustered index on the view.

Thanks

Imran

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

Be aware that indexed views are good, but if you use them, you are increasing the load on your database. This view has to be maintained whenever you change data in the underlying tables, so if these are transactional tables, this can slow down the updates on that table(s)

anil_kumar32 Ten Centuries Points: 1378 More actions · Answer 4

[font="Times New Roman"]

Thanks a lot guys :-):-)

Regards,

Anil K

[/font]