SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's the best way to count?


What's the best way to count?

Author
Message
jbwa
jbwa
SSC Veteran
SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)SSC Veteran (298 reputation)

Group: General Forum Members
Points: 298 Visits: 220
My reasoning also.
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 237
Query 1 is reliable while 4th one is the fastest...
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1855 Visits: 463
>> I think I know why they can get away with it. It's because Oracle's even harder to use <<

No, I have found Oracle to be way easier to learn and use.

IMHO -- Mark D Powell --
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2861 Visits: 608
You can read another discussion about row count at :
http://ask.sqlservercentral.com/questions/1980/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts?page=1#1983

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
zymos
zymos
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 259
Great question that perfectly helps demo on improving performance by use of already existing system statistics hence avoiding much strain and unnecessary use of system resources to gather the statistics.

Thank you.
Ganesh Kamal
Ganesh Kamal
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 78
Is there any performance difference in the below given three queries.
Note: Col1 is NOT NULL column

SELECT COUNT(*) FROM Sales.SalesOrderDetail;

SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;

SELECT COUNT(0) FROM Sales.SalesOrderDetail;
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11132 Visits: 12004
gkganeshbe (12/26/2012)
Is there any performance difference in the below given three queries.
Note: Col1 is NOT NULL column

SELECT COUNT(*) FROM Sales.SalesOrderDetail;

SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;

SELECT COUNT(0) FROM Sales.SalesOrderDetail;

Not really. The second one with COUNT(Col1) may take a very small performance hit during the parse and bind phases, to check whether there is a Col1 column and if it's nullable, but that is an extremely short amount of time.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Dave62
Dave62
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3247 Visits: 2777
gkganeshbe (12/26/2012)
Is there any performance difference in the below given three queries.
Note: Col1 is NOT NULL column

SELECT COUNT(*) FROM Sales.SalesOrderDetail;

SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;

SELECT COUNT(0) FROM Sales.SalesOrderDetail;


Good question. I ran all 3 of these statements as a batch substituting SalesOrderID for Col1 and displayed the estimated execution plan. The estimated plan showed the cost of each query to be the same at 33%.

Just out of curiosity I then added Query 4 from the question and according to the estimated execution plan it is still the fastest. They all were equally accurate.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search