January 14, 2015 at 1:34 pm
Sean Lange (1/14/2015)
Charles Kincaid (1/14/2015)
Great QOD Steve. I would have not thought that this would have that many issues.Seems that COUNT() does some fun things. I have been using COUNT(1) to mean a count of rows to keep clear in my head that a column name is not involved and I don't have to be concerned with the column contents. The asterisk is a short circuit to the same thing but it looks like it involved columns when that is not true. I have even taken to pronounce the word slightly differently. I say it's your ***-To-Risk.
When I first encountered somebody using COUNT(1) I thought it ordinal position and not a constant. Of course the same person who wrote that always used ordinal position for sorting so it seemed a natural progression. That was many moons ago but I have never liked using count(1) for that very reason. It is all preference of course but I have to stop and think about that constant when I see it.
I use COUNT(1) al the time. I think of it as counting the ones that are returned when you would execute the query with SELECT 1 FROM...
I also always use EXISTS SELECT 1. Just a habit 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2015 at 1:42 pm
Koen Verbeeck (1/14/2015)
Sean Lange (1/14/2015)
Charles Kincaid (1/14/2015)
Great QOD Steve. I would have not thought that this would have that many issues.Seems that COUNT() does some fun things. I have been using COUNT(1) to mean a count of rows to keep clear in my head that a column name is not involved and I don't have to be concerned with the column contents. The asterisk is a short circuit to the same thing but it looks like it involved columns when that is not true. I have even taken to pronounce the word slightly differently. I say it's your ***-To-Risk.
When I first encountered somebody using COUNT(1) I thought it ordinal position and not a constant. Of course the same person who wrote that always used ordinal position for sorting so it seemed a natural progression. That was many moons ago but I have never liked using count(1) for that very reason. It is all preference of course but I have to stop and think about that constant when I see it.
I use COUNT(1) al the time. I think of it as counting the ones that are returned when you would execute the query with SELECT 1 FROM...
I also always use EXISTS SELECT 1. Just a habit 🙂
Yeah I think that people who prefer COUNT(1) also prefer EXISTS(Select 1...). I prefer to use * for both of those. They are of course both doing the same thing. I would find it extremely difficult to change to the other much as I assume you would. 😉 When reading others code I am able to jump back and forth with ease though. Funny how stuff like that is with us wacky humans.
_______________________________________________________________
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 14, 2015 at 1:45 pm
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Sean Lange (1/14/2015)
Charles Kincaid (1/14/2015)
Great QOD Steve. I would have not thought that this would have that many issues.Seems that COUNT() does some fun things. I have been using COUNT(1) to mean a count of rows to keep clear in my head that a column name is not involved and I don't have to be concerned with the column contents. The asterisk is a short circuit to the same thing but it looks like it involved columns when that is not true. I have even taken to pronounce the word slightly differently. I say it's your ***-To-Risk.
When I first encountered somebody using COUNT(1) I thought it ordinal position and not a constant. Of course the same person who wrote that always used ordinal position for sorting so it seemed a natural progression. That was many moons ago but I have never liked using count(1) for that very reason. It is all preference of course but I have to stop and think about that constant when I see it.
I use COUNT(1) al the time. I think of it as counting the ones that are returned when you would execute the query with SELECT 1 FROM...
I also always use EXISTS SELECT 1. Just a habit 🙂
Yeah I think that people who prefer COUNT(1) also prefer EXISTS(Select 1...). I prefer to use * for both of those. They are of course both doing the same thing. I would find it extremely difficult to change to the other much as I assume you would. 😉 When reading others code I am able to jump back and forth with ease though. Funny how stuff like that is with us wacky humans.
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.
I might need some help...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2015 at 1:55 pm
Koen Verbeeck (1/14/2015)
Nice but somewhat easy question (especially after the quite lengthy discussion of NULLs and aggregates last week). Not sure it is worth 2 points.
Personally, I think it was way undervalued. It should have been worth 20 points.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2015 at 2:10 pm
Koen Verbeeck (1/14/2015)
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Sean Lange (1/14/2015)
Charles Kincaid (1/14/2015)
Great QOD Steve. I would have not thought that this would have that many issues.Seems that COUNT() does some fun things. I have been using COUNT(1) to mean a count of rows to keep clear in my head that a column name is not involved and I don't have to be concerned with the column contents. The asterisk is a short circuit to the same thing but it looks like it involved columns when that is not true. I have even taken to pronounce the word slightly differently. I say it's your ***-To-Risk.
When I first encountered somebody using COUNT(1) I thought it ordinal position and not a constant. Of course the same person who wrote that always used ordinal position for sorting so it seemed a natural progression. That was many moons ago but I have never liked using count(1) for that very reason. It is all preference of course but I have to stop and think about that constant when I see it.
I use COUNT(1) al the time. I think of it as counting the ones that are returned when you would execute the query with SELECT 1 FROM...
I also always use EXISTS SELECT 1. Just a habit 🙂
Yeah I think that people who prefer COUNT(1) also prefer EXISTS(Select 1...). I prefer to use * for both of those. They are of course both doing the same thing. I would find it extremely difficult to change to the other much as I assume you would. 😉 When reading others code I am able to jump back and forth with ease though. Funny how stuff like that is with us wacky humans.
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.
I might need some help...
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
_______________________________________________________________
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 14, 2015 at 2:22 pm
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
+1
Don Simpson
January 15, 2015 at 7:32 am
An easy one for me. I use this all the time.
January 15, 2015 at 4:54 pm
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.I might need some help...
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
I guess I'm not as consistent as you guys. I generally use select 1 but sometimes select *, probably depending on what I've been looking at recently. But I always use count(*), never count(1). wouldn't bother to change it if I found it, though.
Tom
January 16, 2015 at 2:35 am
if I run the below query then it gives result as 4. So when I mention column name inside of count function, it omitted the null values in that column.
SELECT COUNT(myid) FROM mytable; Result is 4.
SELECT COUNT(*) FROM mytable; Result is 6.
SELECT COUNT(1) FROM mytable; Result is 6.
+1 super nice Question...
Manik
You cannot get to the top by sitting on your bottom.
January 16, 2015 at 6:57 am
TomThomson (1/15/2015)
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.I might need some help...
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
I guess I'm not as consistent as you guys. I generally use select 1 but sometimes select *, probably depending on what I've been looking at recently. But I always use count(*), never count(1). wouldn't bother to change it if I found it, though.
Not sure I would call it consistency, more like anal retentive.
_______________________________________________________________
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 16, 2015 at 10:16 am
Sean Lange (1/16/2015)
TomThomson (1/15/2015)
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.I might need some help...
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
I guess I'm not as consistent as you guys. I generally use select 1 but sometimes select *, probably depending on what I've been looking at recently. But I always use count(*), never count(1). wouldn't bother to change it if I found it, though.
Not sure I would call it consistency, more like anal retentive.
But to call it anal retentive you would have to admit that the version you want to keep is shit. So perhaps that's not the best description. :w00t:
Tom
January 16, 2015 at 10:33 am
TomThomson (1/16/2015)
Sean Lange (1/16/2015)
TomThomson (1/15/2015)
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.I might need some help...
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
I guess I'm not as consistent as you guys. I generally use select 1 but sometimes select *, probably depending on what I've been looking at recently. But I always use count(*), never count(1). wouldn't bother to change it if I found it, though.
Not sure I would call it consistency, more like anal retentive.
But to call it anal retentive you would have to admit that the version you want to keep is shit. So perhaps that's not the best description. :w00t:
I nearly spit out my coffee Tom. :-D:-D:-D
_______________________________________________________________
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 16, 2015 at 12:39 pm
+2 thanks for the question.
Andre Ranieri
January 17, 2015 at 5:38 pm
Sean Lange (1/16/2015)
TomThomson (1/16/2015)
Sean Lange (1/16/2015)
TomThomson (1/15/2015)
Sean Lange (1/14/2015)
Koen Verbeeck (1/14/2015)
Nope, I'm control freak enough to change it to SELECT 1. I even do it in the scripts generated by SSMS.I might need some help...
If we ever work together we may never finish anything since we will just keep changing * to 1 and 1 to *. I am reminded of the Sneetches story by Dr. Suess. 😛
I guess I'm not as consistent as you guys. I generally use select 1 but sometimes select *, probably depending on what I've been looking at recently. But I always use count(*), never count(1). wouldn't bother to change it if I found it, though.
Not sure I would call it consistency, more like anal retentive.
But to call it anal retentive you would have to admit that the version you want to keep is shit. So perhaps that's not the best description. :w00t:
I nearly spit out my coffee Tom. :-D:-D:-D
"I'm not any kind of retentive. I'm just neat."
-- Bob Newheart
ATBCharles Kincaid
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply