Post reply

Ignore Duplicate ==> Clustered or Non clustered

  • September 10, 2006 at 12:25 pm

    #61332

    Hi All,

     

    I have an applicatiion that does around 1000 insert transactions a day, I also have 4 keys that I use to prevent the entry of duplicates on the table.

    Its currently a Ignore Duplicate NonClustered index, at the moment, the insert transactions are pretty quick, but I am deciding whether or not to have a Clutered type index.

     CREATE  UNIQUE  INDEX [Text_Ignore_Dupe] ON [dbo].[Personprofile]([JoinDate],[Personurn], [Event ) WITH  IGNORE_DUP_KEY,FILLFACTOR = 10  

    I look forward to hearing from you all.

    thanks

    Kindest Regards,

    John Burchel (Trainee Developer)

  • September 10, 2006 at 2:31 pm

    #659549

    John,

    Biggest advantage of Clustered Index is when doing searches cos clusterted index determines the physical order of the data in the table. Clustered Index is useful on columns that are often searched for ranges of values.

    Disadvantage is that Inserts are going to be slower. Cos if you each time u insert a row the data has to be rearranged.

    No Clustered Indexes on the table contains Clustered Indexes key so its advisable to have clustered index on few columns.

    There is no hard and fast rules its something thats dependent on your requirement.In your case it depends on how often the application searches data.

    Hope this helps.

    Thanks

    Sreejith

  • September 12, 2006 at 4:38 am

    #659832

    Thanks for the reply

    Here is the scenario, during inserts no searches will be done on the columns, but after a day or two, when the insert would have definitely finished, then searches can occur on the table.

    Kindest Regards,

    John Burchel (Trainee Developer)

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

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