March 29, 2011 at 9:44 pm
Comments posted to this topic are about the item Creating an Indexed View
March 29, 2011 at 9:47 pm
SQL 2008 a minor glitch in creating the view, but nothing that would detract from answering the question.
The link to the supporting documentation did not work, at least not for me ,,, came back with 404 error.
So all, in all a GOOD QUESTION .... Thanks
March 29, 2011 at 10:25 pm
Thanks for the question, however technically none of the answers are correct, as that view can not be indexed because all the objects aren't referenced by two-part names:
FROM Product P INNER JOIN ProductSubcategory PS
would need to be:
FROM dbo.Product P INNER JOIN dbo.ProductSubcategory PS
(Assuming the schema was dbo.)
But since that wasn't an option I just went with would work work, if the view could have been indexed.
Also, as pointed out the link provided doesn't work, here is one that does: http://msdn.microsoft.com/en-us/library/aa933148(v=sql.80).aspx
March 30, 2011 at 12:18 am
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2011 at 12:25 am
Good question with wrong answer. Actually, nonclustered, unique clustered, and unique nonclustered indexes can be created on indexed views. Another matter that a unique clustered index must be the first index created on a view.
Here is a script that creates a view and three indexes on it:
USE AdventureWorks
GO
CREATE VIEW vw_ProductSubcategory
WITH SCHEMABINDING AS
SELECT P.ProductID as ProductID,
P.Name as Product,
PS.ProductSubcategoryID,
PS.Name AS Subcategory
FROM Production.Product P INNER JOIN Production.ProductSubcategory PS
ON P.ProductSubcategoryID = PS.ProductSubcategoryID
GO
-- Cannot create nonunique clustered index on view 'vw_ProductSubcategory'
-- because only unique clustered indexes are allowed
CREATE CLUSTERED INDEX idx_ProductSubcategory_1
ON vw_ProductSubcategory (ProductID, ProductSubcategoryID)
GO
-- This statement runs perfectly
CREATE UNIQUE CLUSTERED INDEX idx_ProductSubcategory_2
ON vw_ProductSubcategory (ProductID, ProductSubcategoryID)
GO
-- And this one runs perfectly
CREATE NONCLUSTERED INDEX idx_ProductSubcategory_3
ON vw_ProductSubcategory (ProductID, ProductSubcategoryID)
GO
-- Even this one runs perfectly
CREATE UNIQUE NONCLUSTERED INDEX idx_ProductSubcategory_4
ON vw_ProductSubcategory (ProductID, ProductSubcategoryID)
GO
March 30, 2011 at 12:25 am
Sorry but the answer to the question you have provided is wrong, or you are confusing the question
BOL has the following,
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX (Transact-SQL).
To get the answer correct you need to select Answer 3 but you can also create Non-Clustered Index on the view as long as you have a Unique Clustered Index.
Your question asked which of the SQL Statements could be used so there was no one right answer, answer 3 is the basis of creating more indexes on the view.
March 30, 2011 at 1:22 am
Hello,
The question is confusing, because the four type of index can be created on a view, but only the first index must be a UNIQUE CLUSTRED index !
Requirements for the CREATE INDEX Statement
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX (Transact-SQL).
The CREATE INDEX statement must meet the following requirements as well as the regular CREATE INDEX requirements:
The user that executes the CREATE INDEX statement must be the view owner.
The following SET options must be set to ON when the CREATE INDEX statement is executed:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF. This is the default setting.
If the database is running in 80 compatibility mode or earlier, the ARITHABORT option must be set to ON.
When you create a clustered or nonclustered index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).
The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.
If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.
An imprecise expression that forms the value of an index key column must reference a stored column in a base table underlying the view. This column may be a regular stored column or a persisted computed column. No other imprecise expressions can be part of the key column of an indexed view.
March 30, 2011 at 2:15 am
It seems to be a common thread with a few of these questions which is that you have to try and second guess what the questioner meant to ask rather than what was actually asked. This one being a case in point. Tecghnically you can create any of the index types, providing as already stated, that a unique clustered index is the first to be created.
Anyway I feel better now that I have had my morning grump!
March 30, 2011 at 2:20 am
Confusing or not, I'd say by elimination you have to come up with the answer that is given as correct.
If the aim of QOTD is to create interest, this is a winner!
March 30, 2011 at 2:28 am
I agree that the given answer could have made it obvious that the restriction only applies to the first index you create on the view, but interesting question nonetheless.
March 30, 2011 at 3:17 am
Joined the fray to make exactly the same point as previous contributors ref. a clustered index has to be created first, then other indexes may follow. 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.
So, with my schemabinding fox shot here are other thoughts. I am always intrigued by differences between Oracle and SQLServer under the hood and those of my colleagues interested in cross ports may find this interesting. In SQLServer, the view index is serialised to disk for performance and optimisation reasons and has to be updated in line with updates to the base table. This put me in mind me of Oracle's Materialized Views (MViews) that I have used for the last n versions of the db. The entire view is serialised to disk greatly improving retrieval speed where the optimizer picks up the MView (while slowing down base table DML as the downside). The SQLServer requirement to create a clustered index first off on schemabound views is to generate the materialised image of the view. However by doing this, Microsoft have forced the creation of what Oralce calls Index Organized Tables which are a real performance problem for multiple updates. Oracle's "base" MView is best described as a projected heap image of the base table, allowing us to pick and chose our indexes (or have none at all) to best effect, after MView creation. Moreover, the "schemabinding" in Oracle is optional and there are several concurrency options for maintaining alignment of the MView with the base table.
March 30, 2011 at 3:34 am
Imran Ashraf-452633 (3/30/2011)
Sorry but the answer to the question you have provided is wrong, or you are confusing the questionBOL has the following,
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX (Transact-SQL).
To get the answer correct you need to select Answer 3 but you can also create Non-Clustered Index on the view as long as you have a Unique Clustered Index.
Your question asked which of the SQL Statements could be used so there was no one right answer, answer 3 is the basis of creating more indexes on the view.
Technically the question and answer are correct.
The OP was not asking what indexes can be created on the view, but instead "which of the following SQL statements can you execute to create an index on the view?"
As can be seen from the SQL creating the view, no indexes have yet been created, so this is clearly the first. Therefore the only SQL statement in the list that will successfully create an index at this time is number 3.
Great question by the way and learned something new, which is always good. Hopefully the OP will contribute more questions in future.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
March 30, 2011 at 3:47 am
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.
March 30, 2011 at 6:20 am
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.
Yes. I was looking for none of the above options.. question is way too confusing.
March 30, 2011 at 6:55 am
DugyC (3/30/2011)
Technically the question and answer are correct.The OP was not asking what indexes can be created on the view, but instead "which of the following SQL statements can you execute to create an index on the view?"
As can be seen from the SQL creating the view, no indexes have yet been created, so this is clearly the first. Therefore the only SQL statement in the list that will successfully create an index at this time is number 3.
Great question by the way and learned something new, which is always good. Hopefully the OP will contribute more questions in future.
Agreed, I read it from the standpoint that the view was just created without any indexes.
JohnD
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply