Need Expert advice on my sql query for speed improvement

Question

Need Expert advice on my sql query for speed improvement

Viewing 2 posts - 16 through 16 (of 16 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

GilaMonster (6/28/2010)
Jeff Moden (6/28/2010)
...and the data on each page is also in physical order.
Not necessarily. Rows on a page don't have to be in any specific order, SQL puts them where there's space. There's a slot array at the end of the page that defines what the order of the rows is supposed to be. http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Proof-that-records-are-not-always-physically-stored-in-index-key-order.aspx
I personally prefer to avoid the word 'physical' here, as it leads to misunderstandings. eg the definition for logical fragmentation is the % of pages where the physical order (defined by the Page No) doesn't match the logical order, so if someone believes that indexes are always stored in physical order, what is their understanding of fragmentation?

That's a keeper. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

... and to be sure, I stand corrected. Thanks again for the link to Paul's article on the subject.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)