(excessive crossposting limited to two of the foour groups)

On Tue, 18 Apr 2006 17:57:02 +0200, Peter wrote:

>From this discussion I conclude:
(snip)
>Thanks to all, I know what is the impact of this choice.
>
>Unless someone has to add some new point in this discussion :-)

Hi Peter,

The point I'm missing in your summary is the ease of impact analysis.

If you use SELECT * anywhere (other than an EXISTS subquery), you lose
the possibility to do an impact analysis of a proposed change by
searching the source code (CREATE scripts) of your database.

--
Hugo Kornelis, SQL Server MVP

Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> The point I'm missing in your summary is the ease of impact analysis.
>
> If you use SELECT * anywhere (other than an EXISTS subquery), you lose
> the possibility to do an impact analysis of a proposed change by
> searching the source code (CREATE scripts) of your database.

A good point, but as Peter seems to using the views as a sort of
synonyms, I think he is covered on that point. That is, when you
work with his system, you need to know that the views mirror the
tables in some way.

But in the general case, you are perfectly right.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Alexander Kuznetsov wrote:
>
> Erland Sommarskog wrote:
> > That explains why I haven't heard of it. SQL Server performs no such
> > deliberations, as far as I know.
>
> Well if on the leaf level of a non clustered index you've got an RID, a
> structure looking like (extent#, page#, row_on_page#), and you know
> that matching rows are likely to be stored on adjacent pages, you can
> traverse a range of the index, sort RIDs of matching rows, and read
> every page with matching rows only once. This is why clustering factor
> is a very useful piece of statistics for DB2/Oracle.
>
> If on the leaf level of a non clustered index you've got a bookmark,
> then you just know absolutely nothing about the physical location of
> the rows you need to retrieve. I don't see any way SQL Server could
> utilize clustering factor as long as bookmarks are used to access rows
> from non-clustered indexes (unless it's a heap table, which is not the
> best practice). Please correct me if I'm wrong.

The bookmark does give (some) information about the physical location!

In some situations, sorting on the clustered index key could be of use.
But more information would be need to be used. The rows are physically
stored in the order of the clustered index key. So statistics of the
clustered index can determine the likelyhood that two consecutive
clustered keys (found in the nonclustered index) can be found on the
same page. This approach would guarantee a maximum of one read per page.
Without such a mechanism each bookmark lookup could lead to a physical
I/O, even if the average numbers of rows per page is much higher. This
could occur if there is not enough memory to hold the data pages in
cache, and the bookmark lookups are done out of (clustered index) order.

But even then, it is just an estimate. The index statistics do not give
information about the density of individual pages, and two consecutive
rows could still be on two different pages. So there is a large margin
of error which makes the tradeoff between 'random' bookmark lookups,
sorted bookmark lookups and clustered index scan (with the advantage of
sequential I/O over random I/O) difficult.

Gert-Jan

Gert-Jan,
good point!