Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

What's the best way to count? Expand / Collapse
Author
Message
Posted Thursday, October 28, 2010 10:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 31, 2014 3:56 PM
Points: 223, Visits: 167
My reasoning also.
Post #1012501
Posted Friday, October 29, 2010 8:36 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Query 1 is reliable while 4th one is the fastest...
Post #1013400
Posted Monday, November 1, 2010 7:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 1,378, Visits: 399
>> 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 --
Post #1013824
Posted Tuesday, August 9, 2011 6:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 2,578, Visits: 549
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
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #1156795
Posted Monday, February 27, 2012 11:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
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.
Post #1258416
Posted Wednesday, December 26, 2012 11:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, May 18, 2014 4:48 PM
Points: 414, Visits: 74
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;
Post #1400478
Posted Thursday, December 27, 2012 3:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 6,020, Visits: 8,288
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
Post #1400545
Posted Thursday, December 27, 2012 6:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 2,084, Visits: 2,081
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.
Post #1400600
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse