Index on a computed column

Question

Index on a computed column

Evgeny Garaev

SSCertifiable

Points: 7428
More actions
September 5, 2018 at 8:13 pm

#376920

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

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72625 More actions · Answer 1

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: 469 More actions · Answer 2

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: 3781 More actions · Answer 3

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: 1004339 More actions · Answer 4

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.

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

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: 442467 More actions · Answer 6

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: 286590 More actions · Answer 7

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: 1004339 More actions · Answer 8

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.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

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: 1004339 More actions · Answer 10

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.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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 More actions · Answer 12

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: 5863 More actions · Answer 13

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