Using a column name in a COUNT function

  • Charles Kincaid

    SSChampion

    Points: 13593

    Comments posted to this topic are about the item Using a column name in a COUNT function

    ATBCharles Kincaid

  • roman.asadovsky

    SSC Enthusiast

    Points: 139

    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(*)?

  • Igor Micev

    SSC-Dedicated

    Points: 33108

    Hi,

    Why not use

    select p.[rows] from sys.partitions p

    where p.index_id in (0,1) and p.object_id = object_id('[schema].[TableName]')

    to replace count(1)?

    You already have the count for every table in sys.partitions view.

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • danielfountain

    SSCarpal Tunnel

    Points: 4229

    According to MS sys.partitions.rows "Indicates the approximate number of rows in this partition"

    http://technet.microsoft.com/en-us/library/ms175012.aspx

  • Shahjahandurrani

    SSC Rookie

    Points: 42

    Count(*) doesn't load the entire table. It uses the index to return count.

  • Igor Micev

    SSC-Dedicated

    Points: 33108

    danielfountain (10/15/2013)


    According to MS sys.partitions.rows "Indicates the approximate number of rows in this partition"

    http://technet.microsoft.com/en-us/library/ms175012.aspx

    Hi,

    Microsoft recommends to use the new dynamic views instead of some deprecated for future use

    The same result can be obtained by using this dynamic view as well

    select i.rowcnt from sys.sysindexes i where i.id = OBJECT_ID('[schema].[TableName]')

    and i.indid = 1

    I often use sys.partitions and it always gives out the same result as count(1). If the maintenance of the indexes is regularly done than that info is exact. However, a good remark of you, thanks.

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Brandie Tarvin

    SSC Guru

    Points: 172517

    The best way I've found for using COUNT(MyCol) is to use either the identity column or the primary key column. That way there are no NULL results to worry about.

    SELECT COUNT(NameID) FROM CountTestSET ANSI_NULLS ON;

    SET QUOTED_IDENTIFIER ON;

    IF EXISTS (SELECT object_id

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'[dbo].[CountTest]')

    AND type in (N'U')

    )

    DROP TABLE dbo.[CountTest];

    CREATE TABLE dbo.[CountTest]([NameID] INT NOT NULL IDENTITY(1,1),

    [Name] [nvarchar](max)

    );

    INSERT INTO dbo.[CountTest] ([Name]) VALUES('Sally');

    INSERT INTO dbo.[CountTest] ([Name]) VALUES(NULL);

    INSERT INTO dbo.[CountTest] ([Name]) VALUES('Mary');

    INSERT INTO dbo.[CountTest] ([Name]) VALUES('Jane');

    INSERT INTO dbo.[CountTest] ([Name]) VALUES(NULL);

    INSERT INTO dbo.[CountTest] ([Name]) VALUES('Bob');

    INSERT INTO dbo.[CountTest] ([Name]) VALUES('Tom');

    INSERT INTO dbo.[CountTest] ([Name]) VALUES(NULL);

    SELECT COUNT(NameID) FROM dbo.CountTest; --Gives count of 8

    SELECT COUNT(Name) FROM dbo.CountTest; --Gives count of 5

    DROP TABLE dbo.CountTest;

    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.

  • djjunio

    SSC Journeyman

    Points: 83

    hi, I usually have the following scenario: I have to count the distincts names

    for this I use:

    SELECT COUNT(distinct name) AS [COUNT distinct] FROM [CountTest];

  • Mighty

    SSCrazy Eights

    Points: 8369

    ...This can produce some surprising results. This is because of the way that COUNT() works...

    Why the surprise if COUNT exactly does what is described in e.g. Technet?

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    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.

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • duncanburtenshaw

    Ten Centuries

    Points: 1213

    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.

    +1

  • SMeng

    Newbie

    Points: 7

    I had learned to count on the primary key column, count(RecordID) as NumberOfRecords. However, the suggestion below to query the system tables seems to be an even better solution.

  • Brandie Tarvin

    SSC Guru

    Points: 172517

    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).

    Here are my statements:

    SELECT COUNT(NameID) FROM dbo.CountTest;

    SELECT COUNT(Name) FROM dbo.CountTest;

    SELECT COUNT(*) FROM dbo.CountTest;

    SELECT COUNT(1) FROM dbo.CountTest;

    Attached is the execution plans. Identical in every way except for the selecting on the name.

    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.

  • Jeff Gray

    SSChampion

    Points: 10637

    I've been known to use "SELECT COUNT(*) from SomeTable with (INDEX=1)" so that I DO load the entire table.

    Generally this would only be in a situation where I'm doing raw performance testing.

  • roman.asadovsky

    SSC Enthusiast

    Points: 139

    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.

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

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