Should I use index in this scenario?

I am new to database and am struggling with a problem. I use SQL Server 2005.

I have a table (Audit table) which gets its data from a trigger on another table (main table). The Audit table can not have a PK on it as there could be duplicates. It has around 15 million rows. I am running an update query on it which is resulting in table scans.

I want to know if I add a non clustered index, how I can test its impact on the inserts/deletes. Adter adding the index, would 开发者_运维技巧there be a scenario where the data coming from the trigger will be lost because the server is too busy rebuilding the table/index?

Thanks Vikram

You can always add an IDENTITY column as the primary key and clustered index. Since it's auto-incrementing, you shouldn't have many problems with index fragmentation.

As for losing data from the trigger because the server is too busy rebuilding the index - no, that should never happen.