A Full Table Scan Good for a Lifetime

How many people can say that they’ve done a manual full table scan for their valentine? I can.

When I first met my wife, she introduced herself as “[FirstName] + [LastName]”, however my brain encountered an “OH, WOW!” error and stopped processing after her [FirstName]. I didn’t want to ask her or anybody else what her [LastName] was, lest she think I wasn’t paying enough attention to remember (when, really, the exact opposite was true). In today’s high-tech world, this would not have been a problem, but we met long before the internet and search engines.

Over the next few months, I saw my wife many times at church and at school (I was a graduate student; she was on staff). But never was her [LastName] again presented to me. One day, she stopped by my townhouse when I wasn’t home. She left a note that only said “Call me.”, followed by her [PhoneNumber] and her [FirstName]. I wanted to keep my secret safe, so I had only one option – the phone book. I spent the next several hours conducting a manual scan of the Galveston white pages. I started on the first page and just kept reading until I found her phone number. It appeared in the W’s. At last armed with the knowledge of my valentine’s [LastName], I made the call.

this is precisely the reason SQL Server needs correlated column statistics (it does at the vector level, but not at the histogram).

so then men know that

SELECT * FROM Women

WHERE Attractiveness > 9 AND MonthlyShoppingExpenses < $3000

and women know that

SELECT * FROM Men

WHERE FinancialStability > 9 AND Fidelity > 9 AND IsSingle > 50%

all yield zero or nearly zero rows.

The individual column statistics may indicate 10% on each column condition, leading one to think that the combined AND conditions yields 1%, which will still have a respectable row count output, when in fact, if correlation is taken into account, a drastically different row count.

Yes, I know, get a life, and I have been called MCP before the MS MCP program existed.

Leave a Comment

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at clinics that treat low income patients. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way. Follow @JohnPaulCook