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
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 239
My reasoning also.
Dhruvesh Shah
Dhruvesh Shah
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

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

Group: General Forum Members
Points: 2962 Visits: 493
>> 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4131 Visits: 615
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 263
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
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34189 Visits: 13108
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Dave62
Dave62
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4145 Visits: 2932
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