March 30, 2011 at 12:37 pm
Plenty of people are saying the same thing: poor question, wrong answer (the right answer isn't even one of the available options).
None of those indices can be created on that view, because it refers to tables using 1 part names (this is stated explicity in the SQL 2000 BoL page referenced for the explanation, and remains true in SQL 2005, SQL 2008, and SQL 2008 R2). Even if we brush aside that small problem, the fact that question requires a single answer rather than ticking multiple boxes is silly - 3 of the 4 indixes listed would be possible (if the view were defined using two part names), not just one - although of course a unique clustered index has to be created first.
Tom
March 30, 2011 at 12:39 pm
Well, things seem to have quieted down now, so I'd like to thank all for playing and appreciate the comments, including the constructive criticism 🙂
Sorry about the typo in the CREATE VIEW statement, and sorry if that threw some of yall into thinking it was a trick question. I'm pretty new to SQL Server, and even newer to SSC. But I've been reading the daily newsletters religiously, including the QotD, and I think this more than anything has helped me pass the 70-432 and 70-433 exams. I use the QotD to test my knowledge and logic (not my ability to Google or cut&paste code), and was hoping to give back to the community a little.
I think the discussion for the QotD is great, but lets not get too anal. Just as with the certification exam questions, just choose the best answer. Anyone that's taken any of the exams will concur that a lot of the questions are ambigous and confusing, but hopefully these QotD's will help everyone to prepare and be able to choose the best answer. I think now I know why the cert exams don't let you know which questions you missed - imagine the backlash! 😀
Thanks all!
March 30, 2011 at 12:42 pm
bitbucket-25253 (3/30/2011)
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.
Good point. Without having run the code I concluded that you can indeed create a Unique Clustered Index, Nonclustered Index and a Unique Nonclustered Index on a schema bound view so I thought there were 3 correct answers. But you cannot create the last 2 without having first created the Unique Clustered Index. So yes, I agree with the one correct answer since it is the only one that runs by itself.
I also agree that there is something to learn from the question.
March 30, 2011 at 12:50 pm
Patrick2525 Posted Today @ 2:39 PM
I think this more than anything has helped me pass the 70-432 and 70-433 exams.
CONGRATULATIONS
March 30, 2011 at 12:56 pm
bitbucket-25253 (3/30/2011)
CONGRATULATIONS
Thanks!
March 30, 2011 at 1:01 pm
Patrick2525 (3/30/2011)
I think now I know why the cert exams don't let you know which questions you missed - imagine the backlash! 😀
Ha! So true!
March 30, 2011 at 8:43 pm
When I tried to answer the QOD, the question had been changed for two part query and the answer "none of the above" was there .
Because I know almost nothing about index, I was trying the answers on one of my data, but I only had to tried the first one (CREATE CLUSTERED INDEX ) because the server gave me an error and told me what was the good way to do it: "Cannot create nonunique clustered index on view 'allo' because only unique clustered indexes are allowed. Consider creating unique clustered index instead."
So, I got the wright answer, but what is special is that I learned more than just the purpose of that question. I always create my views with the manager. So, I learned today how easy it is to create a view with a query . And it is not all: because I always thought that the word "go" in a query window is not really a command for SQL, I never use it. So I was surprised to see an error message while creating the view: "'CREATE VIEW' must be the first statement in a query batch."
!?? :pinch:
Finaly I found the problem: I had "use myDataBase;" at the beginning of the query, and for the first time since I write queries, I had to put a "Go" after it and before the query!
Learned a lot today! Thank you very much!
Would that be too much if I ask what would be the query to see the name of indexes on a view? I am asking that because I tried the wizard "Full-text index\Define full text index" on my view (the view is called "allo") and it gave me the error "A unique column must be defined on this table/view.", but if I tried again the command CREATE UNIQUE CLUSTERED INDEX etc. , it gives the error "The operation failed because an index or statistics with name 'idx_blabla' already exists on view 'allo'" and that proves that there is an index...
A unique clustered index is not a unique column?
March 31, 2011 at 2:03 am
Patrick2525 (3/30/2011)
I think the discussion for the QotD is great, but lets not get too anal. Just as with the certification exam questions, just choose the best answer. Anyone that's taken any of the exams will concur that a lot of the questions are ambigous and confusing, but hopefully these QotD's will help everyone to prepare and be able to choose the best answer. I think now I know why the cert exams don't let you know which questions you missed - imagine the backlash! 😀
Thanks for the question. I did it yesterday, and didn't notice the two-part name "issue", and didn't read the question as "which of the following SQL statements can you use to create an index on the view whether the unique one required to make it an indexed view has already been created or not" - so I went for the best answer and got it correct!
Maybe it could have been worded slightly differently, but I thought it was a good question anyway - and it was pretty obvious to me what it was asking.
March 31, 2011 at 5:32 am
tilew-948340 (3/30/2011)
what would be the query to see the name of indexes on a view?
Try this one:
SELECT i.*
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('the_name_of_your_view')
ORDER BY i.name
tilew-948340 (3/30/2011)
A unique clustered index is not a unique column?
A unique clustered index may contain more than one column. You need a unique index on exactly one non-nullable column.
March 31, 2011 at 6:43 am
confusing
March 31, 2011 at 6:05 pm
Hello vk-kirov
vk-kirov (3/31/2011)
Try this one ...
Works fine!
vk-kirov (3/31/2011)
A unique clustered index may contain more than one column. You need a unique index on exactly one non-nullable column.
I needed a little reading here and there, but I finaly got it.
I have so much to learn as DBadmin and it is realy easier when someone help you to go on the right direction.
Thanks!
April 1, 2011 at 8:21 am
Sorry, wrong answer ... clustered requires two part table names
April 12, 2011 at 2:23 pm
This is a good question, though it is confusing but it as per the question, it is said that which of the statements can be executed to create index on the view.
April 20, 2011 at 9:41 pm
If want to stick with the answer then tweak the question like this --
Which Index would you create first ?
As all indexes are applicable.
November 3, 2012 at 3:30 am
Good Question..
Before giving answer I first read about WITH Schemabinding ON abd got to know that table name that we use in view statement should be wirte using schema else it will throw an error and unique clustere index should be create on that view. so, in the question itself the table name is used without any schema bindings so I thing it was confusing for me to give the anwer for this question with confusing responses.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply