What's the count?

  • Slick84


    Points: 5602

    Comments posted to this topic are about the item What's the count?



    SSC Guru

    Points: 281252

    Thanks for the question. Nice job.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Arjun SreeVastsva


    Points: 7135

    Nice Question ....Today i learned this from Central...Good Question

  • jshailendra


    Points: 2260

    Good question..never thought of using count(*) for 2 tables in one T-SQL...thanks for question

  • aamirjani4u

    SSC Rookie

    Points: 39

    count is basically. Counting the each record of that table.

    For Example. Table A has 3 rows then count = 3. if table B has 4 Rows then count=4.

    If combine both tables then 3 * 4 = 12 rows and count = 12

  • Carlo Romagnano


    Points: 22024

    if one table has one o more rows and the other is void, the result is 0.

    NumberOfRows * 0 = 0

  • Diyas

    SSCarpal Tunnel

    Points: 4845

    In Point of Interview Very Intelligence Question ......Nice...hmmmmm My Answer is Correct............!

  • kaspencer

    SSCarpal Tunnel

    Points: 4453

    That's is a good question, illustrating the need for careful joins!

    For those that don't know, this kind of result is called a "cartesian product", and if you allow it to happen in a real-world application with even a few hundred rows in either table, you are in for something of an explosion of complaints from your users, to say the least.


    You never know: reading my book: "All about your computer" might just tell you something you never knew!

  • nigel.


    Points: 11631

    Very good question.

    However I think that the explanation is a bit lacking, and could explain in more detail what is happening here and what a 'cross join'/'cartesian product' is.

    Here are some links that may help:

    Cross Join

    Cartesian Product

  • Daniel Bowlin


    Points: 34566

    This one definitely made me think a minute or two.

  • cajun_sql


    Points: 1842

    Nice one - thanks. I just "recovered" from a Cartesian Product, myself. 🙂

  • wall str33t

    SSC Veteran

    Points: 253

    A better question might be is there a difference between count(*) and count(col).

    Many people don't know the subtleties between them.

  • sknox


    Points: 12294

    wall str33t (3/11/2010)

    A better question might be is there a difference between count(*) and count(col).

    Many people don't know the subtleties between them.

    Rather than re-answer that question, I'll provide a link to where you can find that information:

    http://www.sqlservercentral.com/Forums/FindPost879366.aspx -- My post explaining how COUNT_BIG works

    http://www.sqlservercentral.com/Forums/FindPost879538.aspx -- Oleg's excellent test setup illustrating my explanation

    These are from the discussion of the March 9 QotD by agrawal.prakriti, about COUNT_BIG. The only difference between COUNT_BIG and COUNT is that COUNT_BIG returns a BIGINT rather than an INT. For official reference:

    http://msdn.microsoft.com/en-us/library/ms190317.aspx -- Microsoft's documentation for COUNT_BIG

    http://msdn.microsoft.com/en-us/library/ms175997.aspx -- Microsoft's documentation for COUNT

    Feel free to write and submit a QotD based on this info.

  • Oleg Netchaev


    Points: 5272

    Carlo Romagnano (3/11/2010)

    if one table has one o more rows and the other is void, the result is 0.

    NumberOfRows * 0 = 0

    This is a very good feature of the count function, to still return 0 if there is nothing to count. This makes it different from other aggregates, which will return null if there is nothing to consider. For example,

    use AdventureWorks;


    declare @one_or_more table


    DepartmentID smallint,

    [Name] nvarchar(50)


    declare @void table (DepartmentID smallint, [Name] nvarchar(50));

    -- insert 10 records into @one_or_more and leave @void empty

    insert into @one_or_more


    top 10 DepartmentID, [Name]

    from HumanResources.Department;

    select count(*) RecordCount, min(A.DepartmentID) DepartmentID

    from @one_or_more A, @void B;


    resulting in

    RecordCount DepartmentID

    ----------- ------------

    0 NULL

    illustrates the point. The cartesian product does not return anything because the @void has no records, the min(A.DepartmentID) is therefore null though the value of the first department in the @one_or_more is actually 1, but the count shines here as it still returns 0, not null. I am not saying that implementation of the count is better, but I find this feature extremely useful.

    This is a very good question, really liked it.


  • Steve Cullen


    Points: 5598

    My definition of a good QOD: Anytime I get the correct answer 😀

    Good Question!

    Converting oxygen into carbon dioxide, since 1955.

Viewing 15 posts - 1 through 15 (of 21 total)

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