October 20, 2017 at 10:22 am
Hi
What is the difference between * and count(1) if we write
select * from table1
Declare @num as integer
select @num = count(1) from table1.
Thanks
October 20, 2017 at 10:55 am
One will return all of the columns and data from table1, the other will return nothing and just store the value 1 in the variable @num (presuming at least 1 row exists in table1).
Best way to learn (in my opinion) is to try it out. They are select statements so it should cause minimal impact on your system, but I still recommend doing testing on TEST systems, not live.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 20, 2017 at 11:07 am
bmg002 - Friday, October 20, 2017 10:55 AMOne will return all of the columns and data from table1, the other will return nothing and just store the value 1 in the variable @num (presuming at least 1 row exists in table1).
Best way to learn (in my opinion) is to try it out. They are select statements so it should cause minimal impact on your system, but I still recommend doing testing on TEST systems, not live.
The bolded part is inexact. It will store the number of rows of the table. Without a GROUP BY, it will always return an integer value, with a GROUP BY it might return NULL.
October 20, 2017 at 11:25 am
Luis Cazares - Friday, October 20, 2017 11:07 AMbmg002 - Friday, October 20, 2017 10:55 AMOne will return all of the columns and data from table1, the other will return nothing and just store the value 1 in the variable @num (presuming at least 1 row exists in table1).
Best way to learn (in my opinion) is to try it out. They are select statements so it should cause minimal impact on your system, but I still recommend doing testing on TEST systems, not live.The bolded part is inexact. It will store the number of rows of the table. Without a GROUP BY, it will always return an integer value, with a GROUP BY it might return NULL.
Right... yes... count(1) will count the number of rows. For some silly reason my brain interpreted it as just "1" not "count(1)".
Thanks for the correction 🙂
It would still return NULL if there were no rows in the table though, right?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 20, 2017 at 11:33 am
bmg002 - Friday, October 20, 2017 11:25 AMLuis Cazares - Friday, October 20, 2017 11:07 AMbmg002 - Friday, October 20, 2017 10:55 AMOne will return all of the columns and data from table1, the other will return nothing and just store the value 1 in the variable @num (presuming at least 1 row exists in table1).
Best way to learn (in my opinion) is to try it out. They are select statements so it should cause minimal impact on your system, but I still recommend doing testing on TEST systems, not live.The bolded part is inexact. It will store the number of rows of the table. Without a GROUP BY, it will always return an integer value, with a GROUP BY it might return NULL.
Right... yes... count(1) will count the number of rows. For some silly reason my brain interpreted it as just "1" not "count(1)".
Thanks for the correction 🙂
It would still return NULL if there were no rows in the table though, right?
Not without a GROUP BY. It will still return 0 if there are no groups defined.
Here's the code that shows it:
DECLARE @test-2 TABLE( a int);
select count(1) from @test-2;
October 20, 2017 at 11:44 am
Luis Cazares - Friday, October 20, 2017 11:33 AMbmg002 - Friday, October 20, 2017 11:25 AMLuis Cazares - Friday, October 20, 2017 11:07 AMbmg002 - Friday, October 20, 2017 10:55 AMOne will return all of the columns and data from table1, the other will return nothing and just store the value 1 in the variable @num (presuming at least 1 row exists in table1).
Best way to learn (in my opinion) is to try it out. They are select statements so it should cause minimal impact on your system, but I still recommend doing testing on TEST systems, not live.The bolded part is inexact. It will store the number of rows of the table. Without a GROUP BY, it will always return an integer value, with a GROUP BY it might return NULL.
Right... yes... count(1) will count the number of rows. For some silly reason my brain interpreted it as just "1" not "count(1)".
Thanks for the correction 🙂
It would still return NULL if there were no rows in the table though, right?Not without a GROUP BY. It will still return 0 if there are no groups defined.
Here's the code that shows it:
DECLARE @test-2 TABLE( a int);
select count(1) from @test-2;
Good to know! For some reason I thought it would be null. Guess I should have followed my own advice of "try it out".
But going back to the OP, I was just thinking about this... Did you mean what is the difference between:SELECT COUNT(*) FROM table1
andSELECT COUNT(1) FROM table1
?
If so, they are equivalent.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply