Creating an Indexed View

  • Patrick2525

    Mr or Mrs. 500

    Points: 547

    Comments posted to this topic are about the item Creating an Indexed View

  • Ron McCullough

    SSC Guru

    Points: 63877

    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

    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]

  • UMG Developer

    SSChampion

    Points: 13482

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

  • vk-kirov

    SSCertifiable

    Points: 7686

    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

  • Imran Ashraf-452633

    SSCommitted

    Points: 1969

    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.

  • khelloufsofiane 6183

    SSC Eights!

    Points: 862

    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.

  • ipounder

    SSCommitted

    Points: 1541

    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!

  • paul s-306273

    SSChampion

    Points: 10615

    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!

  • paul.knibbs

    SSCoach

    Points: 15270

    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.

  • dioscoredes

    Old Hand

    Points: 361

    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.

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

    Hall of Fame

    Points: 3804

    Imran Ashraf-452633 (3/30/2011)


    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.

    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]

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    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.

  • Surii

    Default port

    Points: 1477

    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.

  • John M Dennis

    SSC Enthusiast

    Points: 148

    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 45 total)

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