Post reply

count(*)

  • February 14, 2008 at 10:57 am

    #71316

    Hi all,

    I'm running a query wanting to see all the records that qualify the conditions in the query.

    I'm coming up with zero, which is correct.

    But when I run the same query wanting to see record count instead of the actual records, I'm coming up with 479. This is the same query, identical conditions, but two different results.

    Has anyone seen this before?

    thx

  • February 14, 2008 at 11:11 am

    #778496

    Never seen that myself. Shouldn't be possible.

    Post queries please?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 14, 2008 at 11:18 am

    #778500

    Here you go

    --Query 1

    select count(*)

    from [dbo].[vSection] AS [t0],

    [dbo].[vTalentCriteria] AS [t1],

    [dbo].[vTalentProfileAttribute] AS [t2],

    [dbo].[vTalent] AS [t3],

    [dbo].[vTalent] AS [t4]

    where (t1.Crit=1335)

    and t0.sec_Name='Ethnic Wardrobe Items'

    and t1.CritSectionID=t0.sec_Id

    and t2.tpat_Attribute_Id=t1.Crit

    and t3.Id=t2.tpat_TalentProfile_Id

    and t3.Id=t4.Id

    --Query 2

    from [dbo].[vSection] AS [t0],

    [dbo].[vTalentCriteria] AS [t1],

    [dbo].[vTalentProfileAttribute] AS [t2],

    [dbo].[vTalent] AS [t3],

    [dbo].[vTalent] AS [t4]

    where (t1.Crit=1335)

    and t0.sec_Name='Ethnic Wardrobe Items'

    and t1.CritSectionID=t0.sec_Id

    and t2.tpat_Attribute_Id=t1.Crit

    and t3.Id=t2.tpat_TalentProfile_Id

    and t3.Id=t4.Id

  • February 17, 2008 at 3:07 pm

    #779212

    Hi,

    The query is exactly same. Is this kind of one time behavior? or you are always getting like this?

    It would be better if you give us the data also, so that can debug the problem.

    Thanks -- Vj

    http://dotnetvj.blogspot.com

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • February 17, 2008 at 10:55 pm

    #779232

    Really strange.

    Can you run a checkdb on the database, just to make sure that there's no corruption lurking. You can also try rebuilding the indexes on those tables

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 17, 2008 at 11:17 pm

    #779237

    Look your execution plan. I think there is a difference. count(*) will probably use index something and index is probably corrupted.

  • February 17, 2008 at 11:23 pm

    #779238

    Hi,

    Colud you please check you column values.

    If it containings null values, while counting null values the query will not count it as a record , but while count(*) it will do.

    I am not sure but it may also be a possiblity.

    Mohammad Irfan
    http://matespoint.blogspot.com
    http://www.irfit.com

  • February 18, 2008 at 9:09 am

    #779349

    Thank you all for your suggestions.

    I'll check the db for any incostistencies.

    thanks again

Viewing 8 posts - 1 through 7 (of 7 total)

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