Home Forums SQL Server 2012 SQL 2012 - General Should this index be clustered? RE: Should this index be clustered?

  • November 14, 2017 at 12:18 pm

    #1968069

    Lynn Pettis - Tuesday, November 14, 2017 12:00 PM

    Not sure what you mean by duplication of data, unless you mean that the nonclustered index is duplicating all the data from the table.  My question would be how are the inserts and deletes being done?  Is this table like a queue, first in first out?  If so, I would make the index a clustered index and do it such that new data is inserted at the end and old data pulled from the front, just like a queue.

    Yes, that's what I meant. Each row is written once on the table and then basically copied on the nonclustered index (except for the bit column).
    The inserts are being done either by batch loads or  whenever the system performs an action that affects the data from tables referenced by MainTableID and SomeOtherID. The deletes are being done after the row has been evaluated and assigned to the correct location.
    This is not a FIFO, as there are some way to establish precedence based mainly on the MilestoneID. Ideally, everything should be deleted before the next batch load comes which would contain thousands of new rows.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2