Creating indexes on computed columns

  • Manie Verster

    SSCertifiable

    Points: 7020

    Comments posted to this topic are about the item Creating indexes on computed columns

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • UMG Developer

    SSChampion

    Points: 13482

    I think you need to check the answer key for this question. It lists "Column references can pull data from multiple rows." as one of the options you have to pick, but then in your explanation you say "No column reference pulls data from multiple rows."

    And the BOL article you link to says 'No column reference pulls data from multiple rows.", so it seems that "Column references can pull data from multiple rows." is not a correct choice. Either that or you meant for the can to be can't.

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

    Wow, this is so ridiculous. The referenced BOL page clearly states:

    No column reference pulls data from multiple rows

    The answer, however includes the opposite and still claims it as a correct option. As a matter of fact even the wording of all listed options matches the BOL word by word except it does not include the word NO for the multiple rows option.

    Oleg

  • Kingston Dhasian

    SSCoach

    Points: 19794

    UMG Developer (9/29/2010)


    I think you need to check the answer key for this question. It lists "Column references can pull data from multiple rows." as one of the options you have to pick, but then in your explanation you say "No column reference pulls data from multiple rows."

    And the BOL article you link to says 'No column reference pulls data from multiple rows.", so it seems that "Column references can pull data from multiple rows." is not a correct choice. Either that or you meant for the can to be can't.

    I agree. The answer key for the question is wrong. The explanation is correct as per BOL. I think the editors will rectify this soon.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • honza.mf

    SSCertifiable

    Points: 5519

    Just another 0=1



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Carlo Romagnano

    SSC-Insane

    Points: 21746

    Here the script that states "No column reference pulls data from multiple rows"

    create function aaaa() returns int

    as

    begin

    return (select SUM(a) from aza )

    end

    GO

    create table aza(a int,b as dbo.aaaa())

    create index idx_aza on aza(b) -- raise error: function is NON-DETERMINISTIC

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Though I am right, I lose point 🙁

    Thanks

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    The first answer (All functions that are referenced by the expression are deterministic and precise.) is incorrect as well. As long as the column is persisted, imprecise functions can be used.

    Best Regards,

    Chris Büttner

  • michael.kaufmann

    SSCrazy

    Points: 2816

    UMG Developer (9/29/2010)


    I think you need to check the answer key for this question. It lists "Column references can pull data from multiple rows." as one of the options you have to pick, but then in your explanation you say "No column reference pulls data from multiple rows."

    And the BOL article you link to says 'No column reference pulls data from multiple rows.", so it seems that "Column references can pull data from multiple rows." is not a correct choice. Either that or you meant for the can to be can't.

    Fully agree with UMG Developer.

    And I'd also agree with Chris Büttner--but in order to gain a point, just stick with the wording in BOL and disregard anything else, as computed columns need to be persisted in order to be used in an index (and the question was only about 'computed columns' but not about 'persisted computed columns'). 😎

    Regards,

    Michael

  • paul.knibbs

    SSCoach

    Points: 15270

    I carefully left that third option unchecked because I thought it was the author attempting a bit of a trick by having just that one worded differently from BOL, too!

  • Imran Ashraf-452633

    SSCommitted

    Points: 1909

    Like the many others I have select the 3 options and not selected Column references can pull data from multiple rows. From what I can tell I was right like the others so give me my 1 point:angry:

  • kaspencer

    SSCarpal Tunnel

    Points: 4233

    Yet another error. I won't tolerate any mealy-mouthed excuses, such as those which we have experienced in the past.

    Go to the very bottom of the class, and stay there with your dunces cap on, until I permit you to move.

    [And give me my point]

    Kenneth Spencer.

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • george sibbald

    SSC Guru

    Points: 104200

    please see http://msdn.microsoft.com/en-us/library/ms189292.aspx, the third answer is incorrect, I am sure Steve will rectify the situation.

    this actually follows on from the question two days ago, which is why I answered it.

    ---------------------------------------------------------------------

  • William Vach

    SSCarpal Tunnel

    Points: 4741

    The explanation contradicts answer number 3 being true.

  • rtelgenhoff

    SSC Eights!

    Points: 928

    Yes,

    http://msdn.microsoft.com/en-us/library/ms189292.aspx#BKMK_persisted states that:

    "You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement."

Viewing 15 posts - 1 through 15 (of 29 total)

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