Does creating a clustered index break my application

Question

I am using the SQL Server 2008 R2 Database tuning advisor to tune one database which is used by a 3 rd party ERP system. At the moment this ERP application does have some serious performance issues in specific actions performed by users.

I used SQL Server profiles to trace their production database for few hours during busy time of the day. Last night I let the database tuning advisor chew the trace and it came up with a lot of recommendations and a promised performance gain of 89% !!

So now, I have understand that from the recommendations I should first create recommended clustered and unique indexes and then redo the trace and tuning.

Is it possible that by creating a clustered index I some how break the application functionality ?

Answer 1

Is it possible that by creating a clustered index I some how break the application functionality ?

No, creating CLUSTERED INDEX / UNIQUE INDEX certainly won't break your application from a functional perspective since these would be helpful during retrievals where the index would be consulted instead of performing a table scan. They don't impose any constraint on how the data should be stored in the tables like integrity constraints (PRIMARY KEY, UNIQUE KEY, FOREIGN KEY etc.,).

Answer 2

Clustered index may break app performance in case of update and insert new records. Clustered index tell SQL to place records in order as defined in clustered index.

If new records will break this order, SQL must move many records in table to ensure clustered index order for each new record.