January 30, 2015 at 2:51 pm
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/
January 30, 2015 at 3:09 pm
I think someone wanted to know: what is the best way to determine the number of rows in a query?
Like I said, there are many ways to answer it, but all academic and standard documents I have ever read all states COUNT(*) and I never seen any say COUNT(1). Thus if a new programmer is raising this question, should we tell him/her to code it any way you want as long as it gives the correct result, or should we tell him that use COUNT(1) because it is more efficient, or should we tell him to use the universal standard, which is COUNT(*)? The main benefit of using a common standard is that the code is self documenting and easy to read and maintain. If someone prefers to put COUNT(59) because that's the year they were born, or COUNT(1), I would never allow it in our shop, because it is a bad habit and one bad habit breeds another bad habit and it makes the code more expensive to create and maintain.
January 30, 2015 at 3:24 pm
SQLMaister (1/30/2015)
I think someone wanted to know: what is the best way to determine the number of rows in a query?Like I said, there are many ways to answer it, but all academic and standard documents I have ever read all states COUNT(*) and I never seen any say COUNT(1). Thus if a new programmer is raising this question, should we tell him/her to code it any way you want as long as it gives the correct result, or should we tell him that use COUNT(1) because it is more efficient, or should we tell him to use the universal standard, which is COUNT(*)? The main benefit of using a common standard is that the code is self documenting and easy to read and maintain. If someone prefers to put COUNT(59) because that's the year they were born, or COUNT(1), I would never allow it in our shop, because it is a bad habit and one bad habit breeds another bad habit and it makes the code more expensive to create and maintain.
With a new developer I would explain that you can do either. I would also explain that they both will return the same thing and generate identical execution plans. I would then explain that it is most common to use count(*) and that is what I prefer. I would certainly not tell them to use count(1) because it is more efficient, because it isn't. I think you might find that the universal standard of count(*) is not as universal as you think. In terms of a new developer that is being molded it is best to inform them of more than 1 way to do something. Then explain how you prefer to see it done and explain why. It all comes down to presentation of the concept. If you come in guns blazing and say "this is the one true way to do this" then you are doing the junior developer a disservice. They need to understand that it isn't the only way and that in our shop we do it with this standard. You should also explain that they may see other things going on and they need to understand why.
_______________________________________________________________
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 3:41 pm
mark.finch (1/30/2015)
and what about Count(0) ? Is that the same result as Count(1)?
ANY literal value, or non-column name value, acts exactly the same as count(*).
select count(*) from myTable
select count(1) from myTable
select count('the patriots are going to win') from myTable
select count(NEWID()) from myTable
select count(getdate()) from myTable
These all do exactly the same thing.
Don Simpson
January 30, 2015 at 3:42 pm
DonlSimpson (1/30/2015)
mark.finch (1/30/2015)
and what about Count(0) ? Is that the same result as Count(1)?ANY literal value, or non-column name value, acts exactly the same as count(*).
select count(*) from myTableselect count(1) from myTable
select count('the patriots are going to win') from myTable
select count(NEWID()) from myTable
select count(getdate()) from myTable
These all do exactly the same thing.
Or even this:
select count(1/0) from myTable
_______________________________________________________________
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 3:52 pm
Sean Lange (1/30/2015)
Or even this:
select count(1/0) from myTable
Ooo, I like that! That could break somebody's brain when they stumbled up it.
This is cool to the appropriate nerds too:
select count(distinct 1) from myTable
select count(distinct NEWID()) from myTable
Distinct with a literal will always return 1, but with newid will give you the table's row count.
Don Simpson
January 30, 2015 at 4:36 pm
If someone asks you how to do something, it is usually best to just provide the correct answer. Most people get confused if you first explain the wrong way and then the correct way. Sure if I see a questionable code in a review then I would explains the pros and cons for the various methods. But now that we have figured this really complicated issue of how to count the rows in a result can someone explain something very simple. (Assuming MS SQL 2008+) When is it better to use a temporary table vs a declared table? Also when is it better to use a LEFT OUTER JOIN vs. (NOT EXISTS sub query) to determine rows that are missing from one table based on another table (i.e. Parent without Children)?
January 30, 2015 at 7:04 pm
Even when I first wrote this I never anticipated this much debate or this level of vitriol. The point was that COUNT(column_name) may not produce the results that you think that it might. The COUNT(1) is a matter of style. Yes any constant expression will work. I fell out laughing at the one over zero that was mentioned. It must be taking NaN as a constant. 😎
If you hate my COUNT(1) thing you are going to have a good time with a QoD that I'm working up.
How many columns are returned by this query?SELECT [*] FROM [FROM] ORDER BY [2] DESC
ATBCharles Kincaid
January 31, 2015 at 7:10 pm
I was somewhat surprised too that people would take this so personal - it is only SQL code after all. To the new question you posted. The number of columns depends on the number of columns in table Order, it would return all columns. The query would fail if there is only one column because the request is to order on the second column.
February 1, 2015 at 12:32 am
Thank you for that. You have also confirmed that my idea for a QoD has some validity. I'll try to send you the create table script and please keep the answer to yourself. It it designed to be confusing. It is about what you can not what you should do.
ATBCharles Kincaid
February 1, 2015 at 2:47 am
Charles Kincaid (1/30/2015)
Even when I first wrote this I never anticipated this much debate or this level of vitriol. The point was that COUNT(column_name) may not produce the results that you think that it might. The COUNT(1) is a matter of style. Yes any constant expression will work. I fell out laughing at the one over zero that was mentioned. It must be taking NaN as a constant. 😎If you hate my COUNT(1) thing you are going to have a good time with a QoD that I'm working up.
How many columns are returned by this query?
SELECT [*] FROM [FROM] ORDER BY [2] DESC
It's because you start by saying COUNT(*) is slow in some circumstances and that people try COUNT([Name]) instead, point out that can be subtly different and then say you use COUNT(1) instead. Which comes across as implying COUNT(1) is somehow better, or at least different, to COUNT(*)
COUNT ([Name]) can certainly catch out some unseasoned developers, but it doesn't help anyone to give them the impression that going through their codebase and replacing COUNT(*) with COUNT(1) is going to help their performance.
February 2, 2015 at 9:07 am
Thank you. I learned something about what I took for granted!
February 3, 2015 at 8:17 pm
Is there a downside to using the IsNULL function? For example, using the COUNT script when I try:
SELECT COUNT(IsNULL([Name], ''))
I get 8 as the result
February 4, 2015 at 1:18 am
dmcquade (2/3/2015)
Is there a downside to using the IsNULL function? For example, using the COUNT script when I try:SELECT COUNT(IsNULL([Name], ''))
I get 8 as the result
It'll probably be slower than COUNT(*), unless the optimizer manages to spot what you're trying to do. Not worth trying it though, COUNT(*) will always be the fastest way because SQL Server has an explicit optimization to ensure so.
February 4, 2015 at 7:24 am
andycadley (2/4/2015)
dmcquade (2/3/2015)
Is there a downside to using the IsNULL function? For example, using the COUNT script when I try:SELECT COUNT(IsNULL([Name], ''))
I get 8 as the result
It'll probably be slower than COUNT(*), unless the optimizer manages to spot what you're trying to do. Not worth trying it though, COUNT(*) will always be the fastest way because SQL Server has an explicit optimization to ensure so.
It would be a little slower but count(*) is no faster than count(1) or count(any other nonNULL value). There is no internal optimization for count(*) versus counting other values. It just counts the rows.
That being said, COUNT(IsNULL([Name], '') is rather silly. Why bother with the column here when you are going to end up counting rows. Just use count(*) instead.
_______________________________________________________________
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/
Viewing 15 posts - 76 through 90 (of 113 total)
You must be logged in to reply to this topic. Login to reply