Creating an Indexed View

Question

Post reply

Creating an Indexed View

Viewing 15 posts - 16 through 30 (of 44 total)

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

M&M

SSC-Insane

Points: 21700

March 30, 2011 at 7:08 am

#1305417

Thanks for the question.

M&M

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 2

I will look at this later and likely clean up the points. However from my reading, the view is created. There are no indexes on it, so the only index you can out on it is a unique clustered index. Once that is done you can add others, but right after that code, there is only one correct answer

Patrick2525 Mr or Mrs. 500 Points: 547 More actions · Answer 3

bitbucket-25253 (3/29/2011)
The link to the supporting documentation did not work, at least not for me ,,, came back with 404 error.

Sorry about that - I lost the last character during the cut&paste. The link should be:

http://technet.microsoft.com/en-us/library/aa933148(SQL.80).aspx

Patrick2525 Mr or Mrs. 500 Points: 547 More actions · Answer 4

Koen Verbeeck (3/30/2011)
Nice question, but the answer is confusing. The first index should be a unique clustered index, but you can add more non-clustered indexes later on.
http://msdn.microsoft.com/en-us/library/ms191432.aspx

Sorry I wasn't more clear... The title of the QotD was 'Creating an Indexed View', so I assumed that it was understood that we were creating the first index. Any indexes after that would just be more indexes on the Indexed View.

🙂

Patrick2525 Mr or Mrs. 500 Points: 547 More actions · Answer 5

dioscoredes (3/30/2011)
As an Oracle OCP the hardest part of the exam for me is to learn the way the question setter wants the question answered. With our questioner-of-the-day I would surely have been a gone goose in his examination. Can I put it to the group that it would be helpful to moderate our questions-of-the-day before they are released, to take out these wrinkles before going to press.

Actually, I've been doing a lot of the MCTS Self-study guides and exams from various vendors the past few months to prepare for the exams (passed 2 recently). This question came from one of those practice exams, although I changed the view definition. It was one that stumped me and I thought would be a good one for the community. So this should give you a good idea of what to expect on the real exams 😉

Patrick2525 Mr or Mrs. 500 Points: 547 More actions · Answer 6

Carlo Romagnano (3/30/2011)
The correct answer is "None of the above"
http://msdn.microsoft.com/en-us/library/ms188783.aspx
Tables must be referenced by two-part names, schema.tablename, in the view definition.
I want back my points.

Sorry about that - I must have left that off as I typed on my 'internet-enabled' computer as I transcribed from my SQL Server computer (which doesn't have internet). But since None of the Above wasn't an option, I think you could figure it from there. I wasn't trying to stump anyone with symantics - the question was how to create an indexed view.

And you didn't 'lose' points, so I can't give them back 😛

dioscoredes Old Hand Points: 391 More actions · Answer 7

Fair comment. Thanks for the workout!

Men who wish to know about the world must learn about it in its particular details. - Heraclitus

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

Patrick2525 (3/30/2011)
Carlo Romagnano (3/30/2011)
The correct answer is "None of the above"
http://msdn.microsoft.com/en-us/library/ms188783.aspx
Tables must be referenced by two-part names, schema.tablename, in the view definition.
I want back my points.
Sorry about that - I must have left that off as I typed on my 'internet-enabled' computer as I transcribed from my SQL Server computer (which doesn't have internet). But since None of the Above wasn't an option, I think you could figure it from there. I wasn't trying to stump anyone with symantics - the question was how to create an indexed view.(
And you didn't 'lose' points, so I can't give them back 😛

Are these meant to stump or "accidental" - note that all the indexes have the same name as the name of the correct answers index.

So if I did apply the correct answer I could not create the additional indexes due to the error of attempting to create a "duplicate" index?

Answer 1

CREATE CLUSTERED INDEX idx_ProductSubcategory ON

vw_ProductSubcategory (ProductID, ProductSubcategoryID)

Answer 2

CREATE NONCLUSTERED INDEX idx_ProductSubcategory ON

vw_ProductSubcategory (ProductID, ProductSubcategoryID)

Answer 4

CREATE UNIQUE NONCLUSTERED INDEX idx_ProductSubcategory ON

vw_ProductSubcategory (ProductID, ProductSubcategoryID)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Cliff Jones SSChampion Points: 10547 More actions · Answer 9

For reasons already stated, I wasn't sure how to go on this one? So I guessed that the author thought that the create view syntax was correct which would make 3 answers correct. So then I assumed the author thought that you could create a Clustered NONunique index on a schema bound view. So this was an exercise in logic.

But a good topic and close to a good question.

Patrick2525 Mr or Mrs. 500 Points: 547 More actions · Answer 10

bitbucket-25253 (3/30/2011)
Are these meant to stump or "accidental" - note that all the indexes have the same name as the name of the correct answers index.
So if I did apply the correct answer I could not create the additional indexes due to the error of attempting to create a "duplicate" index?

Not sure if I understand your question (see how hard it is to be unambiguous?), but the QotD was which statement could be used to create an indexed view (after the view had been created using the given CREATE VIEW statement). Only one of those statements would succeed.

The QotD wasn't which statement should be executed first. If someone wanted to create another index after that, then of course they would have to use a name that isn't already being used.

Rodrigo Siqueira de Souza Hall of Fame Points: 3140 More actions · Answer 11

Good question, but the answers are not clear. There is no 100% correct option...

gregg_dn SSCommitted Points: 1596 More actions · Answer 12

Got it wrong but learned something. I agree that the question could've been a little clearer.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ron McCullough SSC Guru Points: 63877 More actions · Answer 14

So if I did apply the correct answer I could not create the additional indexes due to the error of attempting to create a "duplicate" index?
Not sure if I understand your question (see how hard it is to be unambiguous?), but the QotD was which statement could be used to create an indexed view (after the view had been created using the given CREATE VIEW statement). Only one of those statements would succeed.
The QotD wasn't which statement should be executed first. If someone wanted to create another index after that, then of course they would have to use a name that isn't already being used.

The question as stated is:

Which of the following SQL statements can you execute to create an index on the view?

My line of reasoning, after deciding which was the correct answer was:

1. Asked myself why couldn't other indexes be created, when I knew that additional indexes could be created.

2. Carefully examined the code to create the additional indexes.

3. Noted that the names of the additional indexes that could be created were identical as the name of the correct index.

4. Ruled out creating those indexes based on knowing that I could not have multiple indexes on a view or table with identical names.

5. Did not think it was my right to change the T-SQL code listed as possible answers.

6. Ergo the light lit and there was only one answer - which is the answer you identifed as the correct answer.

Then was agast at those who did not read the proposed answers very, very carefully.

Now all the quibbling aside. The objective of the QOD is to TEACH or TEST ones knowledge of SQL Server. Which your question has done .

So do not be discouraged by the comments on this particular question, for its publication has taught you a thing or two or more. Please think of other QODs and submitt them so all may learn more about SQL Server.

Strictly as aside. I have had 38 QODs published. Of those, with 3 of them, "I shot myself in the foot" as the saying goes. But like a rubber band I snaped back and kept at it. And in double and tripple checking my proposed QODs learned a great deal. Hope you do the same as you submitt additional QODs.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Rose Bud Hall of Fame Points: 3147 More actions · Answer 15

Patrick2525 (3/30/2011)
Actually, I've been doing a lot of the MCTS Self-study guides and exams from various vendors the past few months to prepare for the exams (passed 2 recently). This question came from one of those practice exams, although I changed the view definition. It was one that stumped me and I thought would be a good one for the community. So this should give you a good idea of what to expect on the real exams 😉

I liked the question. Learned something. I didn't attempt to debug the CREATE statement and I assumed I was supposed to pick which of the 4 statements could be executed next. Don't hesitate to submit again. This member of the community thanks you!