SQLMaister (1/30/2015)
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.
LOL. I never stated that it was preferred. You stated that using * is preferred and you stated it is the only correct way. There is no documentation that states that either method is preferred or performs better. That is because they are the same thing.
Neither MS help nor the ANSI-92 states that one is better or preferred over the other.
I think you have totally missed the point I was trying to make which is to not state that your opinion is fact unless you can back it up. In this case, you can't because your opinion is not based on fact, it is based on personal preference. In this particular case your personal preference is also mine but that doesn't mean I am going to tell somebody else their code is wrong because I don't like it.
_______________________________________________________________
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/