Post reply

How Index Created on Duplicate Data

  • December 16, 2009 at 6:16 am

    #214753

    Hi All

    I am surprised with one problem

    One Day , One sql server developer reported problem that

    when he executed query like

    select count(*) from tableA , It shows 100 Reocrds

    and when he exected like

    select count(*) from tableA with (Nolock) , It shows 150 Records

    You will suggest that , there is lock on some records but nothing is there

    because I stop & Restart SQL Server and put even in single user mode , rebuild SQL Log also

    when I group by with primary key column , there is no duplicate records

    After Expanding a lot of time on this problem , I found error

    when I remove all primary key from columns & Again executeed that group by query , I found some duplicate records in the table

    After Deleting Duplicate Records from table & recreating keys on those column , found no problem means Both result are same

    So , Question is , How Duplicate records can reside in primary key composite columns

    or How is it possible to create primary key on composite columns having duplicate key

    or How is it possible to insert duplicate data into composite primary key columns

    Can anybody help regarding this matter

    Thanks

    Ghanshyam

  • December 16, 2009 at 6:32 am

    #1092955

    It is not possible to create a primary key on columns that have duplicates, it is not possible to insert duplicate values for a composite primary key.

    However using nolock can cause fake 'duplicates' by reading rows more than once. See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    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

  • December 16, 2009 at 8:52 am

    #1093069

    Good points made in the article linked there. I assumed the extra records were uncommitted transactions and hadn't considered page splits etc when using nolock / read uncommitted leading to records being read again etc. Interesting!!

  • December 16, 2009 at 11:35 am

    #1093231

    Just as an aside. Never rebuild the transaction log. That's something that should only be done if there are certain nasty types of corruption/database damage and no backup. If can lose data.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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