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

select count(*) or select count(OrderID) Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 8:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:52 AM
Points: 291, Visits: 558
Which one is fast to get count of records?
1)select count(*) from order
2)select count(OrderID) from order (OrderID is primary key)
I tested in 3894000 records table but both them are done in 0 second
Post #967453
Posted Wednesday, August 11, 2010 8:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
adonetok (8/11/2010)
Which one is fast to get count of records?
1)select count(*) from order
2)select count(OrderID) from order (OrderID is primary key)
I tested in 3894000 records table but both them are done in 0 second


They'd have the same execution plans I'd imagine, so the same performance in this particular case.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #967462
Posted Wednesday, August 11, 2010 8:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #967485
Posted Wednesday, August 11, 2010 11:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
The optimizer figures out that they are the same since a PRIMARY KEY cannot be NULL and you get the same performance. The table row count (NOT records! quit thinking this is a mag tape file system) is available at the table level, so three is no need to scan the table.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #967627
Posted Wednesday, August 11, 2010 11:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
CELKO (8/11/2010)
The table row count (NOT records! quit thinking this is a mag tape file system) is available at the table level, so three is no need to scan the table.


SQL will scan something to get a row count (from SELECT COUNT(*) or SELECT Count(<not nullable column>)). Not the table, unless there are no nonclustered indexes, but it will scan an index to do the row count, the one with the fewest leaf pages.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #967663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse