Creating indexes on computed columns

Question

Creating indexes on computed columns

Manie Verster

SSCertifiable

Points: 7152
More actions
September 29, 2010 at 9:49 pm

#224566

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)

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

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

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

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

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

Just another 0=1

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

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 5

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

Hardy21

SSCrazy Eights

Points: 9708

September 30, 2010 at 1:23 am

#1229481

Though I am right, I lose point 🙁

Thanks

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

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

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

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

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

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

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 SSCertifiable Points: 5476 More actions · Answer 13

The explanation contradicts answer number 3 being true.

rtelgenhoff SSC Eights! Points: 928 More actions · Answer 14

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."