Need help for SQL query

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  September 7, 2009 at 8:44 pm

  #1050280

  If I can't build a reputation on SQL skills, I am forced to build one on bad puns. 😛

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  September 7, 2009 at 9:59 pm

  #1050292

  Paul White (9/7/2009)

  ---

  PP (9/7/2009)

  ---

  I just ran the script given by Paul completely, I was just trying to understand how thats happening.Thats it.

  I mean, there were 114 records with 2009 as year and rest all 2010, but when we execute that query, its returning only 365 records!

  I know this much that if you use any function on a column having index, the search will go for a table (heap) scan and not an index scan..

  Here not able to understand whats happening.

  The happen to be only 365 days in 2010 :w00t:

  Try:

  SELECT any_date_year, COUNT_BIG(*) AS row_count

  FROM dbo.DateIndexTest

  GROUP BY

  any_date_year

  ORDER BY

  any_date_year;

  You'll see that there are 114 rows (1 per day) in 2009, 365 in 2010, 365 in 2011, and 156 in 2012 🙂

  Ohh what a shame, I just got carried away and thinking something else. Point here was about the 'Index seek'! thanks Paul. 🙂

  ---------------------------------------------------------------------------------

• jcrawf02

  SSC-Insane

  Points: 24198

  More actions

  September 9, 2009 at 7:21 am

  #1051070

  Bob Hovious (9/7/2009)

  ---

  If I can't build a reputation on SQL skills, I am forced to build one on bad puns. 😛

  Damn, I no longer have a unique business strategy...

  ---------------------------------------------------------
  How best to post your question[/url]
  How to post performance problems[/url]
  Tally Table:What it is and how it replaces a loop[/url]

  "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

• Paul White

  SSC Guru

  Points: 150467

  More actions

  September 9, 2009 at 7:47 am

  #1051112

  jcrawf02 (9/9/2009)

  ---

  Damn, I no longer have a unique business strategy...

  [font="Courier New"]Msg 2627, Level 14, State 1, Line 1

  Violation of PRIMARY KEY constraint 'PK_BusinessStrategy_UserID'. Cannot insert duplicate key in object 'dbo.Puns'.

  [/font]

  Nice avatar.

  @PP: You're welcome.

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi