Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Creating indexes on computed columns Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 9:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
Comments posted to this topic are about the item Creating indexes on computed columns

Manie 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)
Post #995722
Posted Wednesday, September 29, 2010 9:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #995723
Posted Wednesday, September 29, 2010 10:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #995726
Posted Wednesday, September 29, 2010 11:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:25 AM
Points: 2,660, Visits: 4,733
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/
Post #995740
Posted Thursday, September 30, 2010 1:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:20 AM
Points: 1,382, Visits: 1,315
Just another 0=1



See, understand, learn, try, use efficient
© Dr.Plch
Post #995761
Posted Thursday, September 30, 2010 1:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 2,541, Visits: 2,409
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
Post #995762
Posted Thursday, September 30, 2010 1:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
Though I am right, I lose point

Thanks
Post #995771
Posted Thursday, September 30, 2010 1:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #995774
Posted Thursday, September 30, 2010 2:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #995779
Posted Thursday, September 30, 2010 2:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:41 AM
Points: 1,593, Visits: 5,631
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!
Post #995783
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse