Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MCSA 70-461 --Question 2


MCSA 70-461 --Question 2

Author
Message
psingla
psingla
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1249
chapter 17 lesson 1
3. What is the scan called when SQL Server scans a clustered index in logical index?
a. Allocation order scan
B. Clustered index scan
c. Index order scan
D. Index order seek

As per training kit exam 70-461 the answer of the above question is C and as per me it should be B...

As per your understanding ,what should be the answer for this?

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
The answer is B.

Does the exam kit has an explanation or link to why C should be the correct answer?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
The key there is that the question is asking about a specific type of clustered index scan, specifically one that runs in the logical order of the index. Hence the answer cannot be B as that's just a general clustered index scan with no mention of any order that it was run in.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


psingla
psingla
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1249
HanShi (7/9/2013)
The answer is B.

Does the exam kit has an explanation or link to why C should be the correct answer?


Writer's Explanation
3. correct answer: c
a. incorrect: When SQL Server scans the data in the physical order, this scan is called
the allocation order scan.
B. incorrect: The Clustered Index Scan is an operator that can scan data in logical or
physical order.
c. correct: When SQL Server scans the data in the logical order of an index, this scan
is called the index order scan.
D. incorrect: Seek is not used for scans.

@Gail:
If this is special case of clustered index scan then that means clustered index scan and index order scan both should be the answer...
No idea how you can we check the index order scan has happened or not?

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
psingla (7/9/2013)
If this is special case of clustered index scan then that means clustered index scan and index order scan both should be the answer...


No.
It's asking for the specific name of the type of scan done when a clustered index is scanned in index logical order. Answering 'clustered index scan' cannot be the correct answer because that's not a specific name that involves the scan ordering.
It's not a special case of a clustered index scan, it's one of the two ways that a clustered index scan can be done.

You can kinda tell and I'm sure there's a few hits on a google search that will discuss it in more detail.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
Thanks Gail, I learned something new again.

I thought that because a clustered index is always ordered, a scan would always be the same. I'm glad I didn't get this question on my exam. But now I know better ;-)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
btw, same things apply to nonclustered indexes, this isn't something special to a clustered index.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


psingla
psingla
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 1249
thanks Gail,,,I got what author is trying to say...

I think instead of clustered index he should have used clustered table to make it clear..


SQL Server uses an allocation order scan for a clustered table if a query does not request
any specific order, if the isolation level is Read Uncommitted, or if you are working in a read-
only environment. When SQL Server scans a clustered index, it can also scan in the logical
order of the index by using the index order scan. In each of these cases, the Clustered Index
Scan iterator is used. SQL Server uses the index leaf–level’s linked list to perform an index
order scan. Index order scans are affected negatively by both logical and physical fragmenta-
tion. The following query does not request any specific ordered result; you can see that the
Clustered Index Scan operator’s Ordered property is False, meaning that SQL Server didn’t
have to return the data ordered, as Figure 17-2 shows.


Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
psingla (7/9/2013)
I think instead of clustered index he should have used clustered table to make it clear..


There's no such thing as a clustered table. There are heaps and tables with clustered indexes, that's all. "Clustered table" is not a term, there's no specific term for 'table with a clustered index' other than 'table with a clustered index' and the scans were of the clustered index, i.e. clustered index scans.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


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