Reputation
310
Top tag
Next privilege 500 Rep.
Access review queues
Badges
2 10
Impact
~9k people reached

  • 0 posts edited
  • 0 helpful flags
  • 28 votes cast
Feb
21
awarded  Popular Question
Feb
16
awarded  Nice Answer
Oct
10
awarded  Popular Question
Sep
24
awarded  Autobiographer
Aug
12
awarded  Necromancer
Jun
19
comment What is the best way to get a random ordering?
Sure, auto-magic is nice. In practice, I've rarely seen a 5 row table scale to millions of rows without notice. TABLESAMPLE() seems to base selection of the number of pages in a table, so the given row size influences what comes back. The point of table sample, at least as I see it, is to give you a good sub-set from which you can select -- kind of like a derived table.
Oct
2
comment What is the best way to get a random ordering?
@db2 -- Sure, and easy enough to add. I've just haven't seen much discussion of the feature...it's one of those hidden gems in SQL Server.
Oct
2
awarded  Yearling
Oct
2
answered What is the best way to get a random ordering?
Oct
27
comment Design Pattern - One of Many Parent Tables
@NathanLong - I've never come across a formal name for this pattern. Octopus isn't bad. I've also thought of it as a one-OF-many join.
Oct
17
awarded  Commentator
Oct
17
comment What factors go into an Indexed View's Clustered Index being selected?
I get what you're saying about behavior & agree the only way to know what's happening is to test. It's not like there is a list of 300 optimizations & rules we can study. Yet, after working with the product 14 hours a day since SQL 4.2 I have a sense of it's personality, as I'm sure you do. Outside of indexed views, I can pretty much tell how adding/removing an index will affect a give query plan. I have a feel for the range of reads on a given query,etc... I'm just looking to become more familiar with the personality in this respect.
Oct
16
comment What factors go into an Indexed View's Clustered Index being selected?
Well said -- that explains it. I'm working through the white papers. Since my view was just a test, I'm working through expanding my data sets significantly (16k rows to a few million in the bigger tables). I'm looking for a work factor where the decision starts to tip towards using the index -- i.e. a rule of thumb.
Oct
16
accepted What factors go into an Indexed View's Clustered Index being selected?
Oct
16
comment What factors go into an Indexed View's Clustered Index being selected?
@SQLKiwi - now we're getting somewhere! What makes the clustered index on a view more expensive to pick than 12 other indexes? On successive runs my sqlplan file does show StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound", but after DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS it shows StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut". Yet the path to materialization stays the same.
Oct
16
comment What factors go into an Indexed View's Clustered Index being selected?
@MaxVernon - just for giggles I tried DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS before each run and multiple combos of 1,2,and 3 fields. No change to the above behavior.
Oct
16
comment What factors go into an Indexed View's Clustered Index being selected?
@SQLKiwi - I did see your comment. I understand forcing the issue (with...),and that SQL picks the 'most efficient' method. Yet, the Profiler images above & execution plans I posted on Gist show statement costs of 0.0527923 (no hint) and 0.00331306 (hint). According to measures of CPU, reads, duration, and statement cost -- the clustered index should have been chosen b/c it's more efficient by at least an order of magnitude. What am I missing?
Oct
16
comment What factors go into an Indexed View's Clustered Index being selected?
@MaxVernon - a single field, no. Explicit fields, yes.
Oct
15
revised What factors go into an Indexed View's Clustered Index being selected?
typo in title
Oct
15
asked What factors go into an Indexed View's Clustered Index being selected?