@ypercubeᵀᴹ > When you say Irish you mean the Irish Gaelic?

There is a great variety of Irish English - indeed, I have difficulty in understanding some (albeit very few) people when they speak English using their local accents - I have to pay close attention...

When I say "Irish" in a linguistic context, I mean the the Irish language (saying "Irish Gaelic" is a bit like saying "Germanic English" or "Romance French" - the Gaelic languages are more closely related to the Romance tongues than the Germanic ones). If I wish to distinguish that from the English that is spoken in Ireland, I'll say "in Irish English" or possibly "Hiberno-English" or "the English that is used in Ireland..."

@Vérace Just out of curiosity, do you have trouble using the reply arrow? Or you just prefer to ping someone and quote the message you're replying to so there's local context? I'm only asking because I've only ever seen you do it I think

@PaulWhite It can sometimes be confusing with several conversations ongoing at one time - just trying to keep things clear - although in this particular case, there's little enough room for ambiguity...

k

@PaulWhite nice.

@J.D. this article is also kind of related: use-the-index-luke.com/sql/where-clause/searching-for-ranges/…

Not showing the difference between an index seek (on unique index) and seek+range scan but showing 2 variations of seek + range scan

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:emp_test,
   |               SEEK:       (date_of_birth, subsidiary_id)
   |                        >= ('1971-01-01', 27)
   |                    AND    (date_of_birth, subsidiary_id)
   |                        <= ('1971-01-10', 27),
   |              WHERE:subsidiary_id=27
   |            ORDERED FORWARD)

the first case with index on (date_of_birth, subsidiary_id) where the seek+range scan is done on date_of_birth part of the index (and then a filter on subsidiary_id part)

and the 2nd, where the index is on (subsidiary_id, date_of_brith) and the index+range scan is done on both columns of the index.

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:emp_test,
   |               SEEK: subsidiary_id=27
   |                 AND date_of_birth >= '1971-01-01'
   |                 AND date_of_birth <= '1971-01-10'
   |            ORDERED FORWARD)

I think a big part of the problem is the emphasis on seeking down the levels of a b-tree to find a leaf page/record. People get that drilled into them so much, they end up thinking every page/record access follows that process.

Inequalities can be misleading as well. People end up associating range scans with different values. An equality search on a non-unique attribute might have a million matches. You wouldn't want to seek down every level of the b-tree for each page or record.

Of course no one teaches you this stuff when you install a database. Most often it is up to the person to educate themselves.

This page (from Use-The-Index-Luke again) has a nice summary but unfortunately not to many details: use-the-index-luke.com/sql/explain-plan/sql-server/operations

that and walks through the leaf nodes to find all matching entries. should be stressed

I guess part of the problem is the name. "Seek" gives the impression that it looks for a single item.

Right

@ypercubeᵀᴹ I was reading this answer, and part 5's comment ("I wonder why") made me think it could be because of DELETEs. When you delete a row you just take 1 off COUNT and you're done with the view. For a MAX, if a row with the MAX value is deleted what is the new MAX? Worst case it would require a table scan to find, which would tank performance.

I've never really bought into that. There are plenty of places in SQL Server where an index or constraint is required and cannot be dropped until a related requirement goes away. In principle, a min or max can always be efficiently computed with the right supporting index, which can be determined from the view definition.

Microsoft have sadly neglected materialized views. Min, max, outer join to name a few.

That said, there's more to this that efficiently maintaining the index. Matching arbitrary subtrees to the view is also a thing. docs.microsoft.com/en-us/archive/blogs/conor_cunningham_msft/…

> 3b. MIN/MAX, STDEV, VARIANCE, AVG – does not preserve enough information to allow incremental maintenance.

True. If there happens to be an index on the MAX'd column it's efficient to find the new MAX.

It can be. There are some long-standing issues with the optimizer and min/max transform to ordered limit 1 with partitioned tables

"Incremental maintenance" phrases what I was thinking quite nicely. For a COUNT the in-process transaction already has all the info it needs to write to the materialized view. For a MAX it would have to find further information outside the working set.

Right. sqlperformance.com/2015/03/sql-plan/…

All that said, there lots of things that sound reasonable, even simple, in principle, but aren't compatible with other other aspects of how the internals work/were designed.

I have written my own synchronized 'view' with min/max based on triggers before. It is not for the faint-of-heart. There are many edge cases and potential race conditions.

@PaulWhite I can imagine concurrent activity and picking the wrong isolation level could mess that up quite quickly.

@MichaelGreen thnx, that makes sense.