Post reply

Delete via Primany Key or Clustered Index

  • July 27, 2009 at 2:45 pm

    #72856

    I have a very large table that is taking a long time to delete records out of

    and I'm wondering if it's better to delete via the Primany Key or the Clustered Index

    create tableHoldMe (ID int identity(1,1),

    Id2 int,

    firstName varchar(20),

    LastName varchar(20))

    CREATE CLUSTERED INDEX [IX_tableHoldMe ] ON [dbo].[tableHoldMe]([Id2]) WITH FILLFACTOR = 90 ON [PRIMARY]

    GO

    Is it better to:

    Delete from tableHoldMe where ID in (1,333,555,4433)

    or

    Delete from tableHoldMe where Id2 in (33,44,55,77)?

  • July 27, 2009 at 2:50 pm

    #1030757

    Based on just what you posted, the second. I don't see a primary key definition (did you leave it out) and hence there's no index on id.

    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 2 posts - 1 through 1 (of 1 total)

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