how to remove duplicate rows in sysobjvalues

  • Hi,

    Can you advise how to remove Duplicated rows in sysobjvalues. or remove the Index as the last option

    I am fed up with DAC access.

    advice if possible with Command line code

    My error text

    "the CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'sys.sysobjvalues' and the index name 'clst'. The duplicate key value is (60, 808494059, 1, 0)."

     

    Warm Regards

    Krish

  • Just out of curiosity sake, can you post the entire CREATE UNIQUE INDEX statement you were running to get this error?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This was an interesting subject that I'd not heard of before.  I checked and found the following article by Paul Randal.  What most interesting is his answer to one of the questions in the discussion of the following article...

    https://www.sqlskills.com/blogs/paul/how-are-default-column-values-stored/

    Here's what I'm talking about...

    shovan says:

    January 24, 2022 at 8:31 pm

    Paul – I’m seeing contention on 2:1:128. What do we store in sysobjvalues in tempdb ?

    Paul Randal says:

    January 25, 2022 at 2:59 pm

    Things like default column values – just like in regular databases. My guess is you’re creating and dropping lots of temp tables through concurrent connections. There was also a bug that was fixed in 2017 CU5 that you may be hitting.

    You posted on a 2019 forum so I'm assuming that your not using 2017 but it does beg the question of...

    Are you up to date on CUs???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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