SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating indexes on computed columns


Creating indexes on computed columns

Author
Message
Imran Ashraf-452633
Imran Ashraf-452633
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1147 Visits: 484
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 pointAngry
kaspencer
kaspencer
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2474 Visits: 836
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
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10234 Visits: 13687
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
William Vach
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3245 Visits: 1741
The explanation contradicts answer number 3 being true.
rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 363
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."
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2756 Visits: 8083
"Me too" on the incorrect options stuff.

But in any case, 1 point is a bit mean for a 4-checkbox multiselect question
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3740 Visits: 2903
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
JestersGrind
JestersGrind
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3611 Visits: 1585
Very disappointing. This is a fairly basic question. A little proofreading could have prevented this from becoming a bad question.



Steve Eckhart
Steve Eckhart
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1088 Visits: 8664
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
gary.mazzone
gary.mazzone
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2626 Visits: 692
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search