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 Thursday, September 30, 2010 2:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:39 AM
Points: 1,115, Visits: 454
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
Post #995788
Posted Thursday, September 30, 2010 3:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 1,791, Visits: 510
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
Post #995795
Posted Thursday, September 30, 2010 3:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 5,886, Visits: 13,044
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.


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

Post #995798
Posted Thursday, September 30, 2010 5:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:21 AM
Points: 2,542, Visits: 1,386
The explanation contradicts answer number 3 being true.
Post #995829
Posted Thursday, September 30, 2010 5:58 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:01 AM
Points: 703, Visits: 327
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."
Post #995840
Posted Thursday, September 30, 2010 6:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 1,805, Visits: 6,580
"Me too" on the incorrect options stuff.

But in any case, 1 point is a bit mean for a 4-checkbox multiselect question
Post #995864
Posted Thursday, September 30, 2010 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:13 AM
Points: 2,917, Visits: 2,536
Manie:

Your answers are wrong. You state that you can pull data from multiple rows, yet BOL which you state as your reference says
No column reference pulls data from multiple rows. For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.


Can't have it both ways.


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #995876
Posted Thursday, September 30, 2010 7:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
Very disappointing. This is a fairly basic question. A little proofreading could have prevented this from becoming a bad question.




Post #995901
Posted Thursday, September 30, 2010 7:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 31, 2014 9:32 AM
Points: 1,009, Visits: 8,661
Actually, if you read carefully, an index on a computed column CAN include multiple rows and columns from other tables. As long as one of the following four conditions are true, then you can use the computed column in an index. Therefore, only the first answer is necessary and the other three are incorrect answers.

The computed_column_expression must be deterministic. A computed_column_expression is deterministic when one or more of the following is true:

All functions that are referenced by the expression are deterministic and precise. These functions include both user-defined and built-in functions. For more information, see Deterministic and Nondeterministic Functions. Functions might be imprecise if the computed column is PERSISTED. For more information, see Creating Indexes on Persisted Computed Columns later in this topic.

All columns that are referenced in the expression come from the table that contains the computed column.

No column reference pulls data from multiple rows. For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.

Has no system data access or user data access.





Steve Eckhart
Post #995904
Posted Thursday, September 30, 2010 7:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:53 AM
Points: 1,954, Visits: 550
I don't mind getting a question wrong, but only when the answer i give is the wrong one. The third choice is clearly incorrect as true when read in BOL.
Post #995921
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse