Check that a specific Group By condition does not exist

• ScottPletcher

  SSC Guru

  Points: 101180

  More actions

  September 11, 2012 at 1:06 pm

  #1536441

  Lynn Pettis (9/11/2012)

  ---

  Just a quick test using 6 million rows:

  Lynn's method:

  Elapsed Time (ms) 8276

  ScottPletcher's first method:

  Warning: Null value is eliminated by an aggregate or other SET operation.

  Elapsed Time (ms) 12244

  ScottPletcher's second method:

  Elapsed Time (ms) 7464

  I ran this several times with very similar results. The third method on 6 million rows is about 1 second faster.

  Interesting.

  Given that I would never anticipate anything close to that number of TemplateIDs in a real world situation, I still prefer my first method, since the entire concept of what is being queried is expressed in the HAVING clause alone, so that query is self-documenting in that sense.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".