Using a column name in a COUNT function

  • Jeff Gray

    SSChampion

    Points: 10637

    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.

  • ryan.shirley

    SSC Journeyman

    Points: 92

    In practice I have noticed count(1) performs much better than count(*).

  • paul s-306273

    SSChampion

    Points: 10545

    Who'd have thought count(*) would generate so much debate...

  • Mighty

    SSCrazy Eights

    Points: 8398

    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.

  • Brandie Tarvin

    SSC Guru

    Points: 172524

    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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Antares686

    SSC Guru

    Points: 125444

    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

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • DataTherapist

    Mr or Mrs. 500

    Points: 510

    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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • jon.berkowitz

    Valued Member

    Points: 54

    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?

  • Antares686

    SSC Guru

    Points: 125444

    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.

  • SQLMaister

    SSC Enthusiast

    Points: 152

    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.

  • gavansmith

    Newbie

    Points: 9

    can through -> can throw ?

Viewing 15 posts - 16 through 30 (of 114 total)

You must be logged in to reply to this topic. Login to reply