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/