245 reputation
16
bio website milesquaretech.com
location Hoboken, NJ
age
visits member for 2 years, 4 months
seen 11 hours ago

Yep, I code in a tux. Don't you?


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?
Sep
29
comment Rules in SQL Server 2005
Just an FYI .... RULES have been marked deprecated in SQL 2005 & later. They exists, but you are cautioned against using them in new development work. msdn.microsoft.com/en-us/library/ms188064%28v=sql.90%29.aspx
Sep
28
awarded  Teacher
Sep
28
answered For a boolean settings table, should I toggle a flag, or insert/delete?
Sep
27
comment Design Pattern - One of Many Parent Tables
Thanks for the input; I agree with most of your comments. I probably in-artfully described the required relationship (excluding solution 2 in your eyes), as it was a made up example. fn1 - understood, i was just simplifying tables a great deal to focus on the problem. fn2 - composite keys and I go way back! Re the lesser coupling schema I understand the simplicity, but personally try to design with DRI wherever possible.
Sep
27
awarded  Scholar