Difference between count(*) and select *

• Dave Morrison

  SSCrazy

  Points: 2017

  More actions

  May 9, 2011 at 11:58 am

  #1321930

  my head hurts 🙂 I was agreeing with you and was meant to be commenting on Jesse's post.

• MyDoggieJessie

  SSC-Forever

  Points: 44486

  More actions

  May 9, 2011 at 12:47 pm

  #1321952

  Hey I'm here to learn as well and am by no means an expert 🙂 Just trying to reiterate something that I was taught (apparently mis-taught) without ever validating it with an execution plan...

  After a simple check, I agree the execution plans are identical. Must have been other reasons during prior times I ran both statements simultaneously to make the COUNT(1) come back faster than the COUNT(*)

  Thanks for clarifying this point.

  ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 9, 2011 at 2:32 pm

  #1321989

  MyDoggieJessie (5/9/2011)

  ---

  Hey I'm here to learn as well and am by no means an expert 🙂 Just trying to reiterate something that I was taught (apparently mis-taught) without ever validating it with an execution plan...

  After a simple check, I agree the execution plans are identical. Must have been other reasons during prior times I ran both statements simultaneously to make the COUNT(1) come back faster than the COUNT(*)

  Thanks for clarifying this point.

  Can happen because of caching the data in the first run (longer), then 2nd run much faster.

  Might not be that problem, but likeliest suspect.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 9, 2011 at 2:33 pm

  #1321990

  Dave Morrison (5/9/2011)

  ---

  my head hurts 🙂 I was agreeing with you and was meant to be commenting on Jesse's post.

  There's a session bug on thsoe buttons. Long story short is that if after you've done the pageload someone posts, all those buttons will quote 1 up (per extra post).

  You're not crazy... or don't take taht as a proof that you're senile :w00t::hehe:.

• Paul White

  SSC Guru

  Points: 150467

  More actions

  May 9, 2011 at 11:37 pm

  #1322088

  GilaMonster (5/9/2011)

  ---

  If there's no where clause on the query, select * from tbl will return all columns, all rows. The presence of nulls changes nothing.

  Here's an interesting exception: SELECT * does not always return all columns in SQL 2008:

  CREATE TABLE dbo.T (A INT SPARSE, B INT SPARSE, C XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

  GO

  INSERT dbo.T (A, B) VALUES (100, 999);

  -- Column C only

  SELECT * FROM dbo.T;

  -- More SELECT * than SELECT * :)

  SELECT A, B, C FROM dbo.T;

  DROP TABLE dbo.T;

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150467

  More actions

  May 10, 2011 at 12:05 am

  #1322095

  Ninja's_RGR'us (5/9/2011)

  ---

  Just to pile on more proof...

  Run this and look at the xml plan generated :

  SELECT COUNT(*) FROM sys.columns

  UNION ALL

  SELECT COUNT(1) FROM sys.columns

  UNION ALL

  SELECT COUNT('Whatever string') FROM sys.columns

  The plan comes up with the same operations for all 3 queries :

  <ScalarOperator ScalarString="Count(*)">

  <Aggregate AggType="countstar" Distinct="false" />

  </ScalarOperator>

  Interestingly, the following returns zero, despite the COUNT(*) references! 😉

  SELECT COUNT(CAST(NULL AS INT)) FROM sys.columns

  Properties window:

  [Expr1018] = Scalar Operator(COUNT(*))

  XML Show plan:

  <ScalarOperator ScalarString="COUNT(*)">

  <Aggregate AggType="COUNT_BIG" Distinct="false">

  <ScalarOperator>

  Of course, the difference is in the aggregate type in the XML show plan, but it's not shown in the properties window!

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Johan Bijnens

  SSC Guru

  Points: 135265

  More actions

  May 10, 2011 at 12:43 am

  #1322112

  SQLkiwi (5/9/2011)

  ---

  GilaMonster (5/9/2011)

  ---

  If there's no where clause on the query, select * from tbl will return all columns, all rows. The presence of nulls changes nothing.

  Here's an interesting exception: SELECT * does not always return all columns in SQL 2008:

  CREATE TABLE dbo.T (A INT SPARSE, B INT SPARSE, C XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

  GO

  INSERT dbo.T (A, B) VALUES (100, 999);

  -- Column C only

  SELECT * FROM dbo.T;

  -- More SELECT * than SELECT * :)

  SELECT A, B, C FROM dbo.T;

  DROP TABLE dbo.T;

  Good catch, Paul

  BOL topic "Using Column Sets" just states this behavior as a caution ( imo not emphasized enough )

  Caution:

  Adding a column set changes the behavior of SELECT * queries. The query will return the column set as an XML column and not return the individual sparse columns. Schema designers and software developers must be careful not to break existing applications.

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me