Index on a computed column

  • Evgeny Garaev

    SSCertifiable

    Points: 6579

    Comments posted to this topic are about the item Index on a computed column

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71808

    Nice question, thanks Evgeny

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Ken Hiatt

    SSC-Addicted

    Points: 444

    I may have picked the wrong answer, but I was right that it wouldn’t work. The basic concept would require the index to be completely rebuilt at least once every day to function as desired and that just doesn’t seem like a practical idea. I’m sure there’s a better way to do it 🙂

  • Budd

    Hall of Fame

    Points: 3769

    Is there EVER a realistic reason to create a column such as that ?, certainly not in a production OLTP database ?

  • Jeff Moden

    SSC Guru

    Points: 996619

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Eric M Russell

    SSC Guru

    Points: 125089

    You can't index the expression used in this example, because it's not deterministic (includes GETDATE function). However, if what you really need is to query Orders created in the past X days, then this can be facilitated with an index keyed on OrderDate.

    Another little known fact is that later editions of SQL Server now support creating indexes on non-persisted computed columns.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Lynn Pettis

    SSC Guru

    Points: 442332

    Eric M Russell - Thursday, September 6, 2018 9:58 AM

    You can't index the expression used in this example, because it's not deterministic (includes GETDATE function). However, if what you really need is to query Orders created in the past X days, then this can be facilitated with an index keyed on OrderDate.

    Another little known fact is that later editions of SQL Server now support creating indexes on non-persisted computed columns.

    As long as it is deterministic.

  • Sean Lange

    SSC Guru

    Points: 286536

    Jeff Moden - Thursday, September 6, 2018 9:22 AM

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    I agree. The question asks if you can create the index to improve the query. The index isn't the issue here at all. It is that you can't create the computed column because it is not a deterministic value. But it does get provide the learning about deterministic computations which I believe is what the OP was trying to accomplish.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Jeff Moden

    SSC Guru

    Points: 996619

    Sean Lange - Thursday, September 6, 2018 10:35 AM

    Jeff Moden - Thursday, September 6, 2018 9:22 AM

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    I agree. The question asks if you can create the index to improve the query. The index isn't the issue here at all. It is that you can't create the computed column because it is not a deterministic value. But it does get provide the learning about deterministic computations which I believe is what the OP was trying to accomplish.

    This question is a classic example of why I don't take tests anymore.  😀  The most correct answer isn't because the column isn't persisted... It's because you can't add an index on this column because it's non-deterministic.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Sean Lange

    SSC Guru

    Points: 286536

    Jeff Moden - Thursday, September 6, 2018 1:07 PM

    Sean Lange - Thursday, September 6, 2018 10:35 AM

    Jeff Moden - Thursday, September 6, 2018 9:22 AM

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    I agree. The question asks if you can create the index to improve the query. The index isn't the issue here at all. It is that you can't create the computed column because it is not a deterministic value. But it does get provide the learning about deterministic computations which I believe is what the OP was trying to accomplish.

    This question is a classic example of why I don't take tests anymore.  😀  The most correct answer isn't because the column isn't persisted... It's because you can't add an index on this column because it's non-deterministic.

    Actually you can't add an index because the column doesn't exist. The column itself cannot be added to the table with the code provided. IF the column existed then the code to generate the index would be fine. So in some ways the most correct answer would be the first one "Yes, because you using a PERSISTED column". However, with the code in the question the column can't exist.

    Given that and the question "Can I create a following index in order to improve my query performance?" the only realistic answer is "NO, because the column DaysFromOrder would not be created because it is not deterministic".

    As you said, tests are terrible because the wording can be so challenging. Gosh this is becoming a "chicken and the egg" discussion.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Jeff Moden

    SSC Guru

    Points: 996619

    Sean Lange - Thursday, September 6, 2018 1:41 PM

    Jeff Moden - Thursday, September 6, 2018 1:07 PM

    Sean Lange - Thursday, September 6, 2018 10:35 AM

    Jeff Moden - Thursday, September 6, 2018 9:22 AM

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    I agree. The question asks if you can create the index to improve the query. The index isn't the issue here at all. It is that you can't create the computed column because it is not a deterministic value. But it does get provide the learning about deterministic computations which I believe is what the OP was trying to accomplish.

    This question is a classic example of why I don't take tests anymore.  😀  The most correct answer isn't because the column isn't persisted... It's because you can't add an index on this column because it's non-deterministic.

    Actually you can't add an index because the column doesn't exist. The column itself cannot be added to the table with the code provided. IF the column existed then the code to generate the index would be fine. So in some ways the most correct answer would be the first one "Yes, because you using a PERSISTED column". However, with the code in the question the column can't exist.

    Given that and the question "Can I create a following index in order to improve my query performance?" the only realistic answer is "NO, because the column DaysFromOrder would not be created because it is not deterministic".

    As you said, tests are terrible because the wording can be so challenging. Gosh this is becoming a "chicken and the egg" discussion.

    The "Chicken and Egg" thing actually has an answer.  The "egg" came first because a chicken can only ever be a chicken.  And egg can mutate to whatever genes got into it.  Supposedly, consider that birds supposedly came from lizards.

    The real conundrum is... who was the first person to look at a chicken and said to themselves "Ok... I'm going to eat the next thing that comes out that chicken's a$$".. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Lynn Pettis

    SSC Guru

    Points: 442332

    Jeff Moden - Thursday, September 6, 2018 5:17 PM

    Sean Lange - Thursday, September 6, 2018 1:41 PM

    Jeff Moden - Thursday, September 6, 2018 1:07 PM

    Sean Lange - Thursday, September 6, 2018 10:35 AM

    Jeff Moden - Thursday, September 6, 2018 9:22 AM

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    I agree. The question asks if you can create the index to improve the query. The index isn't the issue here at all. It is that you can't create the computed column because it is not a deterministic value. But it does get provide the learning about deterministic computations which I believe is what the OP was trying to accomplish.

    This question is a classic example of why I don't take tests anymore.  😀  The most correct answer isn't because the column isn't persisted... It's because you can't add an index on this column because it's non-deterministic.

    Actually you can't add an index because the column doesn't exist. The column itself cannot be added to the table with the code provided. IF the column existed then the code to generate the index would be fine. So in some ways the most correct answer would be the first one "Yes, because you using a PERSISTED column". However, with the code in the question the column can't exist.

    Given that and the question "Can I create a following index in order to improve my query performance?" the only realistic answer is "NO, because the column DaysFromOrder would not be created because it is not deterministic".

    As you said, tests are terrible because the wording can be so challenging. Gosh this is becoming a "chicken and the egg" discussion.

    The "Chicken and Egg" thing actually has an answer.  The "egg" came first because a chicken can only ever be a chicken.  And egg can mutate to whatever genes got into it.  Supposedly, consider that birds supposedly came from lizards.

    The real conundrum is... who was the first person to look at a chicken and said to themselves "Ok... I'm going to eat the next thing that comes out that chicken's a$$".. 😀

    Probably watched other animals eating them and decided to try it. 😀

  • TomThomson

    SSC Guru

    Points: 104773

    Jeff Moden - Thursday, September 6, 2018 1:07 PM

    Sean Lange - Thursday, September 6, 2018 10:35 AM

    Jeff Moden - Thursday, September 6, 2018 9:22 AM

    Kind of a bad "correct" answer.  The reason why it can't be used in an index is because it's indeterminate and whatever reason you chose, the end of it all is that you can't use the column in an index.

    I agree. The question asks if you can create the index to improve the query. The index isn't the issue here at all. It is that you can't create the computed column because it is not a deterministic value. But it does get provide the learning about deterministic computations which I believe is what the OP was trying to accomplish.

    This question is a classic example of why I don't take tests anymore.  😀  The most correct answer isn't because the column isn't persisted... It's because you can't add an index on this column because it's non-deterministic.

    And it's a classic example of why I rarely look at QOTD any more.  The column can't be used in an index because the column doesn't exist. That's the only correct answer.  It isn't a non-deterministic colum, because it's not a column at all - the statement adding the column fails and does not create the column,  A very obvious reason - non-existent columns can't be used in indexes. 
    If it did exist (as it would if the PERSISTED keyword had not been included in the command to add it) couldn't be used in an index because it would be non-deterministic (not "is" non-deterministic - how can something that doesn't exist be non-deterministic or deterministic?  If a non-existent column can have properties - I don't believe it can - the surely the only property it can have is non-existence) NOT because it isn't persisted - there is no requirement in the latest SQL Server that requires a computed column to be persisted for it to be used in an index, so the supposedly correct answer is somewhat misleading.

    Tom

  • Shayn Thomas

    SSCertifiable

    Points: 5603

    Good question, stumped me so proves i need to read into this more,
    ta

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

Viewing 14 posts - 1 through 14 (of 14 total)

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