Jeff Moden (5/17/2012)
No problem. Thanks.I ran the test you included in the Blog entry and actually did get seeks at the .5% and .3% levels instead of scans, but that still absolutely supports what you said. I was having a hard time accepting it all until I realized that you and Craig where talking about things that didn't cover in the index.
Apologies for the confusion. Yeah, my numbers were off too, sorry, it's 0.003 or .3%... and I dropped it in the middle of the two, heh. Whoops. I find most non-clusters are non-covering and used for search mechanics, though you do have the occassional exception for tight data pulls.
Shifting back to the likes of things like calendar tables (keeps coming up because I'm considering writing an article about such things)... your examples seem to exemplify why to not fill it with a bunch of garbage that SQL has functions for. If you don't need to search for it, you might not want to include it in the actual table just because its convenient. Then again, if you use it a lot, maybe. Heh... "It Depends".
Thanks for the feedback and "SQL in the Wild".
An alternative to proc or table is to meet in the middle with a non-persisted calculated column, Jeff. Keeps it out of the data-pages but available on row-usage. I'd recommend being gentle with them though. Somewhere around here I have some tests where I shattered table speed goofing around with them but I'll have to find it and my notes. Another article I started research on and then let fall off the earth.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA