January 30, 2015 at 7:11 am
bah...didn't realize this article was a repost and replied to a comment that was a year and a half old.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 7:15 am
Michel Steiner (1/30/2015)
Under which conditions can count(*) be slow? I'm trying with a 5TB DB, several tables having millions of rows and always get the answer in less than 1 sec.
When you don't have an index.
January 30, 2015 at 7:19 am
Richard Warr (1/30/2015)
Michel Steiner (1/30/2015)
Under which conditions can count(*) be slow? I'm trying with a 5TB DB, several tables having millions of rows and always get the answer in less than 1 sec.When you don't have an index.
But the point is that count(*) is no different performance wise than count(1). If there is no index the performance is the same.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 8:56 am
I'm with you Brandie. I use Count on the primary ID column, unless I specifically want to Count a column that might have nulls, in which case I want it to behave as described (and only count non nulls).
January 30, 2015 at 10:21 am
SELECT COUNT(1) AS [COUNT(1) Should be 8]
, COUNT([Name]) AS [COUNT(Name) Won't be 8]
, COUNT(*) AS [COUNT(*) This is 8 too] FROM [#CountTest];
This will produce the comparision
January 30, 2015 at 10:48 am
I don't get it. There are thousands of ways to get the correct answer, but why make it complicated? There is only one correct standard SQL method. SELECT COUNT(*) provides the number of rows SELECT COUNT(column name) gives the number of rows with NOT NULL values in that column. Any other method may provide the same result, but it makes the code confusing which makes is more expensive to code and maintain. The other methods are NOT more efficient, no matter what people post here. The standard method is the fastest and best method. That is a fact.
January 30, 2015 at 11:49 am
SQLMaister (1/30/2015)
I don't get it. There are thousands of ways to get the correct answer, but why make it complicated? There is only one correct standard SQL method. SELECT COUNT(*) provides the number of rows SELECT COUNT(column name) gives the number of rows with NOT NULL values in that column. Any other method may provide the same result, but it makes the code confusing which makes is more expensive to code and maintain. The other methods are NOT more efficient, no matter what people post here. The standard method is the fastest and best method. That is a fact.
NO it isn't fact, it is your opinion. I personally prefer count(*) but that does NOT mean it is fastest or the best. By your own words it isn't any different from a performance perspective so stating that your personal preference is faster is the same bologna as the people here stating that using count(1) is faster. Keep in mind that what you find confusing [and seriously count(1) is more confusing than count(*)?] does not mean it is confusing so somebody else. You can shout until you are blue in the face but stating that your opinion is fact is inaccurate.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 12:23 pm
Can someone post a reference from any SQL Standards book or academic research or similar work that states that SELECT COUNT(1) is the standard or intended method or syntax to determine the number rows in a select statement? There is no such reference, because it is incorrect. The "1" is a totally arbitrary number, it will work just as well with 0 or 1234567890 or 'ABC' or any other non-null value. Thus, the correct syntax is (*) because it indicates that is not limited to any column or value.
January 30, 2015 at 12:35 pm
SQLMaister (1/30/2015)
Can someone post a reference from any SQL Standards book or academic research or similar work that states that SELECT COUNT(1) is the standard or intended method or syntax to determine the number rows in a select statement? There is no such reference, because it is incorrect. The "1" is a totally arbitrary number, it will work just as well with 0 or 1234567890 or 'ABC' or any other non-null value. Thus, the correct syntax is (*) because it indicates that is not limited to any column or value.
Can you show me any SQL Standards book that states that count(*) is preferred or correct? You continue to state that your opinion is the only correct one. The point I am making is that you should step back a little bit and realize that your opinion is not the only "correct" method. As I said before, I prefer to use count(*) but am not confused that another way to do it is by using any non NULL value (constant or non nullable column).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 1:30 pm
I just ran into this yesterday trying to count the number of NULL records and didn't know why it didn't work as expected, so thank you for this article.
January 30, 2015 at 1:47 pm
A debate on this topic ?
I'm in.
COUNT() sounded so simple, I never bothered to look up on TechNet to see if there was any "fine print".
It is another lesson learned, ASSuming there were no gotchas.
January 30, 2015 at 1:51 pm
Yes I can, actually all books and standard reference SQL documents states that COUNT(*) is the correct way. Check it out yourself, look for example in MS Help file, ANSI 92 standard, etc. all of them say COUNT(*); none of them say COUNT(1). I am not certain about this, but I think most optimizers actually translates COUNT(1) into COUNT(*) before the optimizer evaluates it. The reason why * is better is that the optimizer can then return the count in the most efficient way as determined by the optimizer. But the main practical reason why the * should be used is for consistency, I don't want to have 4 programmers each use their own preference, it is much more efficient if all follow the same standard. Okay, so with that said, please post a any serious reference source that claims that COUNT(1) would in any way be better...
January 30, 2015 at 1:57 pm
SQLMaister (1/30/2015)
Can someone post a reference from any SQL Standards book or academic research or similar work that states that SELECT COUNT(1) is the standard or intended method or syntax to determine the number rows in a select statement? There is no such reference, because it is incorrect. The "1" is a totally arbitrary number, it will work just as well with 0 or 1234567890 or 'ABC' or any other non-null value. Thus, the correct syntax is (*) because it indicates that is not limited to any column or value.
The point is, with SQL Server at least, COUNT(*) and COUNT(1) or indeed COUNT(<any constant>) is totally identical. The optimizer will always use exactly the same method and return exactly the same results in exactly the same time.
January 30, 2015 at 2:04 pm
SQLMaister (1/30/2015)
Yes I can, actually all books and standard reference SQL documents states that COUNT(*) is the correct way. Check it out yourself, look for example in MS Help file, ANSI 92 standard, etc. all of them say COUNT(*); none of them say COUNT(1). I am not certain about this, but I think most optimizers actually translates COUNT(1) into COUNT(*) before the optimizer evaluates it. The reason why * is better is that the optimizer can then return the count in the most efficient way as determined by the optimizer. But the main practical reason why the * should be used is for consistency, I don't want to have 4 programmers each use their own preference, it is much more efficient if all follow the same standard. Okay, so with that said, please post a any serious reference source that claims that COUNT(1) would in any way be better...
In one sentence you state you are not sure how the optimizer works but in your next sentence you claim it as fact. Your preference for using * is fine and would be a perfectly acceptable coding standard in your shop. However, you stating it is the only correct methodology is just simply not based on fact. I was asking for you to show me where it states in any documentation that * is in fact preferred or performs better. Your response was to tell me to look it up. That does not support your stance at all. In fact, according to BOL (the de facto standard for SQL server) it clearly states that you can use either * or an expression. A constant is an expression.
You seem to think that I feel that count(1) will perform better than count(*). We are in agreement that isn't the case. In fact, they are absolutely identical other than the syntax. My point all along has been that you keep using your preference and declaring it as a fact. It is not fact, it is your opinion and preference. You seem to be mixing efficiency at debugging/maintaining code and performance of data retrieval.
To summarize, count(*) and count(1) are identical. The only difference is personal preference or coding standards in a given shop. Functionally they are 100% equivalent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 2:41 pm
From MS SQL Server help. Also see p 124 of ANSI92 SQL standard.
Okay, now I want to see a reference where they state that COUNT(1) is better or preferred...
Syntax
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
Arguments
ALL
Applies the aggregate function to all values. ALL is the default.
DISTINCT
Specifies that COUNT returns the number of unique nonnull values.
expression
Is an expression of any type except text, image, or ntext. Aggregate functions and subqueries are not permitted.
*
Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.
Viewing 15 posts - 61 through 75 (of 113 total)
You must be logged in to reply to this topic. Login to reply