Split Function Usage for Multiple Multi-Select Parameters

Question

Split Function Usage for Multiple Multi-Select Parameters

Viewing 3 posts - 16 through 17 (of 17 total)

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

mikevessey (1/6/2017)
I've been tripped up by this before too
The key point is PHYSICAL .
A clustered index does not determine the order it is stored on disk.
I went to a lecture by itzig ben-gan in 2008 , he showed that the data page that the row is stored on is controlled by your san or disk controller. In the case of mixed extents, you have multiple tables in one 64 kb block.
Your disk is random access, therefore it is not possible to say any data is stored in a linear format.
The clustered index is the sort order of the b-tree, not the order of storage on disk... But let's not pursue this, the original posters question is the point

I don't think that I have been 'tripped up'.

If a table has a clustered index and that index is rebuilt, there is likely to be a strong relationship between the index order and the physical order on disk.

Here's a direct quote from Gail Shaw on the subject (link[/url]):

I suspect this myth came about because, when SQL creates or rebuilds an index, it will try as best as possible to put the pages of the index down in physical order of the index key. Doing so reduces logical fragmentation and allows the read-ahead reads to work as efficiently as possible. This applies only when the index is created, rebuilt or reorganised, not during regular operations.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

mikevessey Valued Member Points: 66 More actions · Answer 2

hey,

nobody said you had been tripped up... in fact I said that I had been tripped up before

in sql 2000 days people always said PHYSICAL storage order. most likely it will be the physical storage order straight after an index build on a single disk.

but after a page split or on a raid array (which we all use) or on a san then this is just a pointless statement.

so the only condition I can see this occurring is on a read only table with a new index on a single disk without RAID

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

mikevessey (1/6/2017)
hey,
nobody said you had been tripped up... in fact I said that I had been tripped up before
in sql 2000 days people always said PHYSICAL storage order. most likely it will be the physical storage order straight after an index build on a single disk.
but after a page split or on a raid array (which we all use) or on a san then this is just a pointless statement.
so the only condition I can see this occurring is on a read only table with a new index on a single disk without RAID

You actually said this (though the emboldening is mine):

"I've been tripped up by this before too"

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel