# SQL experts: Is count(\*) or count(1) better? **URL:** \ **Category:** Factual Questions\ **Created:** [February 12, 2008, 5:28pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297 "2008-02-12T17:28:47Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![taxi78cab](https://avatars.discourse-cdn.com/v4/letter/t/e47c2d/32.png) [@taxi78cab](https://boards.straightdope.com/u/taxi78cab)\ **Post date:** [February 12, 2008, 5:28pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/1 "2008-02-12T17:28:47Z")
I’ve always used count(_) in queries where I was grouping, but one of our DBAs said it’s faster to use count(1). However, I’m not totally confident in his knowledge of these kinds of things so I’m looking for confirmation. Is count(1) really better than count(_) performance-wise for queries such as the following: select State, count(1) from Customers group by State (Obviously, that’s a very simple example. Generally I’m using this on queries with multiple group by fields, where clauses, etc.) Are there situations where count(\*) is better or is count(1) always more efficient? ---
**Author:** ![Sanity\_Challenged](https://avatars.discourse-cdn.com/v4/letter/s/9de053/32.png) [@Sanity\_Challenged](https://boards.straightdope.com/u/Sanity_Challenged)\ **Post date:** [February 12, 2008, 6:49pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/2 "2008-02-12T18:49:45Z")
Most current DB’s will optimize them to be the exact same. Use whatever makes your boss feel smart. ---
**Author:** ![Cerowyn](https://avatars.discourse-cdn.com/v4/letter/c/82dd89/32.png) [@Cerowyn](https://boards.straightdope.com/u/Cerowyn)\ **Post date:** [February 12, 2008, 7:04pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/3 "2008-02-12T19:04:08Z")
As **Sanity Challenged** states, in practical terms, the two are equivalent. The conventional wisdom used to be that **COUNT(\*)** was a special case optimization by SQL engines that didn’t require the actually row data. It’s a safe bet that modern implementations will also recognize the presence of the literal as the same situation. Philosophically, I prefer the asterisk, as the meaning seems to be more intuitive to me. YMMV ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [February 13, 2008, 3:13am UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/4 "2008-02-13T03:13:54Z")
I’m with **Cerowyn** … COUNT(\*) means “count the rows returned”. Any other column name, function or expression means “count the non-nulls”. As the others have said, the db engine might optimize a literal because it’s always non-null, but why rely on that? ---
**Author:** ![Arnold\_Winkelried](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)\ **Post date:** [February 13, 2008, 6:11am UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/5 "2008-02-13T06:11:22Z")
If I were dubious, I would write two queries on a big table: select count (\*) from big\_table select count (1) from big\_table and see which one runs faster. If there is no difference, I would ask the DBA why he says there is a difference. What database system are you using? What version? I would be very surprised if there were any difference between the two methods. ---
**Author:** ![taxi78cab](https://avatars.discourse-cdn.com/v4/letter/t/e47c2d/32.png) [@taxi78cab](https://boards.straightdope.com/u/taxi78cab)\ **Post date:** [February 13, 2008, 2:48pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/6 "2008-02-13T14:48:05Z")
We’re using SQL Server 9.0.3186. I did have my doubts about count(1) being faster, which is why I asked here. Thanks. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [February 13, 2008, 3:53pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/7 "2008-02-13T15:53:47Z")
Nothing like a little empirical testing to throw up some actual results. I did both a SELECT COUNT(1) and a SELECT COUNT(\*) with several GROUP BY clauses on a table that contains over 22 million rows, and on the second execution (to avoid disk latency issues) both queries took 44 seconds to execute and returned the exact same execution plan. This is on SQL Server 9.0.3054. So it looks like there’s no difference at all, and you can crow. Incidentally, the reason I have access to tables with 22 million rows is because I work for a data warehousing company, which is convenient when questions like this are asked. 😉 ---
**Author:** ![Cervaise](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cervaise/32/16693_2.png) [@Cervaise](https://boards.straightdope.com/u/Cervaise)\ **Post date:** [February 13, 2008, 4:48pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/8 "2008-02-13T16:48:30Z")
Only 22 million? More like a data E-Z U-Store-It company. 😉 ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [February 13, 2008, 5:06pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/9 "2008-02-13T17:06:22Z")
[QUOTE=Cervaise] Only 22 million? More like a data E-Z U-Store-It company. 😉 [/QUOTE] Eh, I was using one of the smaller tables in the database, because quite frankly I didn’t want to wait longer than necessary for results. ---
**Author:** ![Cervaise](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cervaise/32/16693_2.png) [@Cervaise](https://boards.straightdope.com/u/Cervaise)\ **Post date:** [February 13, 2008, 5:27pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/10 "2008-02-13T17:27:57Z")
Well okay then. 😛 ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [February 13, 2008, 5:34pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/11 "2008-02-13T17:34:54Z")
How many people do you know who want to store 22 million rows of anything? 😛 ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [February 13, 2008, 8:52pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/12 "2008-02-13T20:52:41Z")
I use count(\*) because \* is ‘all fields’ or ‘a record’ so I am counting records. Counting how many 1 there are is completely nonsensical, even if it does work. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [February 13, 2008, 8:54pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/13 "2008-02-13T20:54:09Z")
[QUOTE=Dervorin] How many people do you know who want to store 22 million rows of anything? 😛 [/QUOTE] Individual bet transactions over six years for thousands of customers. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [February 13, 2008, 9:37pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/14 "2008-02-13T21:37:23Z")
[QUOTE=Lobsang] Individual bet transactions over six years for thousands of customers. [/QUOTE] Oh, we’ve got databases with far higher volumes than that - foreign exchange transactions for a major bank over several years and advertising ratings for all programmes for a major TV channel. I think each of those is over several billion rows in total. I was just going along (tongue-in-cheek) with the comment about 22 million being a Data E-Z U-Store-It Company, and wondering what individual person would need that sort of storage. Am I spending too much time explaining? I suspect so… ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [February 13, 2008, 9:43pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/15 "2008-02-13T21:43:33Z")
Actually our bet transaction DB only has 12,501,479 records. Which is less than half the 23 million. Took my SQL server 32 seconds to count them. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [February 13, 2008, 9:56pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/16 "2008-02-13T21:56:11Z")
I was bored so I ran the query with count(1). Took 31 seconds. So it’s one second quicker. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [February 13, 2008, 11:27pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/17 "2008-02-13T23:27:32Z")
Lobsang - you have to be careful with tests like this because the second one should almost always run faster (all things being equal) because of data caching. The only way to really test fairly is to bring up the server fresh with one user. Then run Q1, Q2. Restart the server fresh again and run Q2, Q1. I’m guessing the \* in count(\*) leads people to the analogy with select \*… they think they’re asking the server to count “everything”. It doesn’t work that way - it’s a special syntax that says give me the rows returned. That in itself doesn’t really require the server to do anything because it probably always counts that anyway. ---
**Author:** ![tanstaafl](https://avatars.discourse-cdn.com/v4/letter/t/ba8739/32.png) [@tanstaafl](https://boards.straightdope.com/u/tanstaafl)\ **Post date:** [February 13, 2008, 11:31pm UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/18 "2008-02-13T23:31:34Z")
Bah. I’m looking at a SQL Server table right now with 31,776,848,544 rows in it. Data space is almost 2 Tb. I’m not going to try count(1). ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [February 14, 2008, 12:16am UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/19 "2008-02-14T00:16:24Z")
[QUOTE=K364] Lobsang - you have to be careful with tests like this because the second one should almost always run faster (all things being equal) because of data caching. The only way to really test fairly is to bring up the server fresh with one user. Then run Q1, Q2. Restart the server fresh again and run Q2, Q1. I’m guessing the \* in count(\*) leads people to the analogy with select \*… they think they’re asking the server to count “everything”. It doesn’t work that way - it’s a special syntax that says give me the rows returned. That in itself doesn’t really require the server to do anything because it probably always counts that anyway. [/QUOTE] The second run (count (1)) was an afterthought upon returning to the thread. I had closed down the server (well, enterprise manager at least) and I actually assumed the second query would run a lot faster due to caching (or to be more accurate - due to knowing I’d run the same or similar query recently) but it didn’t, It was only about three percent faster. ---
**Author:** ![susan\_foster](https://avatars.discourse-cdn.com/v4/letter/s/d9b06d/32.png) [@susan\_foster](https://boards.straightdope.com/u/susan_foster)\ **Post date:** [February 14, 2008, 2:34am UTC](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297/20 "2008-02-14T02:34:10Z")
[QUOTE=Dervorin] Oh, we’ve got databases with far higher volumes than that - foreign exchange transactions for a major bank over several years and advertising ratings for all programmes for a major TV channel. I think each of those is over several billion rows in total. I was just going along (tongue-in-cheek) with the comment about 22 million being a Data E-Z U-Store-It Company, and wondering what individual person would need that sort of storage. Am I spending too much time explaining? I suspect so… [/QUOTE] Are we playing whose data set is bigger? 'Cause the one I am responsible for gets 40-60 million records a day. At one time we were up to 100 million a day. And each record is a couple hundred fields long. Do I win? Susan [Next page](https://boards.straightdope.com/t/sql-experts-is-count-or-count-1-better/437297.md?page=2)