October 15, 2013 at 8:02 am
roman.asadovsky (10/15/2013)
MyDoggieJessie (10/15/2013)
You know that doing COUNT(*) on a table with a lot of columns and a lot of rows can take a lot of time and memory
There is virtually no difference between SELECT(*) and SELECT(1) - the execution plans are identical, and each will produce the same number of logical reads.
That's true, however SELECT * returns all the fields to the output, unlike SELECT 1, thus flooding the buffer with bytes of unnecessary data. That is not the case with COUNT(*) and COUNT(1) though. These are absolutely identical in all aspects, as far as I know.
Please correct me if I am wrong - I would love to learn if there is a difference.
Either one returns only the count. SQL server does the same thing for both queries.
October 15, 2013 at 8:07 am
In practice I have noticed count(1) performs much better than count(*).
October 15, 2013 at 8:08 am
Who'd have thought count(*) would generate so much debate...
October 15, 2013 at 8:14 am
paul s-306273 (10/15/2013)
Who'd have thought count(*) would generate so much debate...
I agree, and that apparently nobody uses a where clause while counting, but that they only are interested in all rows of a table.
October 15, 2013 at 8:37 am
Mighty (10/15/2013)
paul s-306273 (10/15/2013)
Who'd have thought count(*) would generate so much debate...I agree, and that apparently nobody uses a where clause while counting, but that they only are interested in all rows of a table.
On an 8 row table, I don't care about the WHERE clause because I am looking at all the data (the test table in the article). On my work database, though, I do actually pare down the recordset. I'm constantly looking for "order counts" during a certain time frame. Don't need the entire table for things like that.
I also tend to use INNER JOINs a lot for the recordsets I need counted on. Again, using AW terms, counting the order details of the orders that happen within a certain time frame. INNER JOINs are great filters. if the schema is designed correctly.
October 15, 2013 at 8:41 am
Although yes this should just be a common knowledge thing the article doesn't present something that newbies sometimes don't understand and may be looking for the answer why even though documented. Everyone makes this mistake at some point so kudos on doing an article beginners and some experienced people who have never ran across before can use.
As for COUNT(1) versus COUNT(*) my understanding of * is that count actually just uses the under the hood row id for it's count and not the physical whole row. Certain functions have shortcuts for performance reasons like this as taking the entire physical row will use a lot of memory on large tables when there are better methods to do the work from the system perspective. Doing Count (1) acutally will read the and use the rowid with 1 as an aliased column (my understanding) thus adding a bit of overhead that wasn't there to ensure you get an output against all rows. You could put anything in COUNT(0), COUNT('HA'), etc. However even if you use a fixed value the rule around NULL is still checked. Try this example
SELECT COUNT(cast(NULL as int))
FROM dbo.[CountTest]
You will get 0. So using a fixed value does not have any performance improvements to my knowledge.
Example of table with rows containing nothing but null.
declare @x table (idx int null, idt varchar(10) null)
insert into @x values (null, null)
insert into @x values (null, null)
insert into @x values (null, null)
insert into @x values (null, null)
insert into @x values (null, null)
select count(*) from @x
select count(idx) from @x
select count(1) from @x
select count(cast(null as int)) from @x
October 15, 2013 at 9:27 am
Not sure I want to throw this out there, but here goes... I deal with some very large tables and count(*) or count(PrimaryKey) performs horribly at times. When I simply want to know the number of rows in a table, I often use:
sp_spaceused MyTable
October 15, 2013 at 9:36 am
Brandie Tarvin (10/15/2013)
Interesting results on the execution plan. All plans but the COUNT() on Name came backup with only an expected 9B in the row size. The Name count came back much bigger (and off of what the results were).
That is part of the point of the article. COUNT([Column]) if the column is fixed width has to pull in the contents of the column. Experiment with columns of various widths(INT vs. BIGINT).
ATBCharles Kincaid
October 15, 2013 at 9:38 am
paul s-306273 (10/15/2013)
Who'd have thought count(*) would generate so much debate...
I take the Microsoft approach to writing. I get it good enough for release and let the community have at it. 😛
ATBCharles Kincaid
October 15, 2013 at 9:44 am
I think it was a great quick reminder. It's tempting to put a column name in COUNT() thinking that you are somehow being helpful to SQL! just let it do its thing with COUNT(*).
Peter A.R. Johnson
October 15, 2013 at 10:00 am
roman.asadovsky (10/14/2013)
That is well-known behavior of the COUNT(), but the question I have is this: What makes you think that COUNT(1) in any way superior to CONT(*)?
Right you are. With newer versions of SQL Server (2008 and 2008r2) the good folks on the development team seem to have done something fun with COUNT(*). It tends to short-circuit and do the same as COUNT(1).
Experience with earlier versions showed a performance difference that was astounding. Almost to the same level of performance boost that can be obtained by moving some predicated to the JOIN instead of leaving them in the WHERE (but that another subject).
What I would actually like to see is COUNT() to mean to count the number of rows. It would do the same thing as COUNT(1).
ATBCharles Kincaid
October 15, 2013 at 11:01 am
how about:
SELECT ISNULL(name, '') AS [Count_no_NULLs] FROM COUNT_TEST;
that is what you are trying to do right, count the rows that are not NULL?
October 15, 2013 at 11:18 am
Charles Kincaid (10/15/2013)
What I would actually like to see is COUNT() to mean to count the number of rows.
I would like to only have to type COUNT() instead of COUNT(*) or COUNT(1) or anything to get a generic count. Sure it's 1 character, but why make us type something so trivial that could be handled without that.
October 15, 2013 at 1:44 pm
There is no benefit in using select count(1), Select Count(columnname) excludes NULL values, that is the intended purpose. Select count(*) is the most efficient and correct way.
October 15, 2013 at 2:54 pm
can through -> can throw ?
Viewing 15 posts - 16 through 30 (of 113 total)
You must be logged in to reply to this topic. Login to reply