Hi Simon,

The point you are making is absolutely correct, but your example isn't.

The query you posted would probably use a scan on the IX_Person_DateOfBirth table, followed by a bookmark lookup to get forename and surname. When you write: "So your query would then read 2 or 3 pages", you seem to assume that all rows with DOB in the required range are in the same or adjacent pages. Since the clustered index is on the IDENTITY column, this would be highly coincidental - definitely not the kind of coincidence a DBA would want to rely on!

Of course, with the LOB data moved out of row, there is indeed a better chance that some of the matching rows happen to be on the same page - though that chance decreases as the table gets bigger (to wit, if the table totals 1,000 rows, the chance is pretty good; if the table totals a few billion rows - well, you get the picture). So you will probably see SOME improvement, but not as much as your article implies.

The queries that REALLY would take advantage of having more rows per page are the queries that use a (partial or complete) clustered index scan. For instance:

SELECT Forename, Surname
FROM   Person
WHERE  PersonID BETWEEN '623412' AND '624211'

Assuming no gaps in the identity sequence, this query would have to read 800 + X pages if the LOB is stored in the row, but only 10 + X with the LOB stored out of row, with X being equal to the number of levels in the clustered index (probably 2 or 3, maybe 4 if the table is extreme large).

Of course, if you often query the data based on a range of birthdates, you'll probably want to change the index on PersonID to nonclustered and IX_Person_DateOfBirth to clustered - and in that case, your example applies exactly as you wrote it.

Point taken. The 80 rows was due to that being the almost maximum that could fit on one page, so the bookmark lookup would only need to read 1 page rather than 80 pages if the avatar was include in row.

If in your last point about changing the clustered index, a user could end up in the siuation where they filter on dateOfBirth and join to another table on personID i.e

SELECT Address.AddressLine1 ...

FROM Address

JOIN Person on Address.PersonId = Person.PersonId

WHERE Person.DateOfBirth BETWEEN '1 jan 1973' and '1 Feb 2006'

The inclusion of the Avatar in row would really screw performance. If you have a clustered index, changing it from the PK is something that should be done with very careful consideration. Especially in SQL 2000 where you can't include columns in an index at the leaf level.