Как правило, вы должны использовать кластерные индексы только для полей Identity, и автономные номера являются лучшими, поскольку они постоянно растут. Обновление столбца идентификаторов - это то, что вы должны делать раз в тысячелетия, и, разумеется, не во время выполнения приложения, поэтому вам никогда не придется переупорядочивать содержимое страницы с помощью стандартного оператора DML.
Внутри этих ограничений кластерные индексы очень эффективны, но, как вы указали, при неправильном использовании они могут существенно снизить производительность для операторов DML в индексированном поле.
Таким образом, короткий ответ, не используйте кластерные индексы для пользовательских введенных полей.
Что касается некластеризованных индексов, они могут быть главным благом или предостережением, в зависимости от их использования. NCI очень хороши для получения одной или небольшого количества строк, но размер индекса увеличивается с каждым добавленным к нему полем, поэтому сложные индексы с несколькими полями или индексы, которые могут разрешить запрос только к большому количеству строк в по крайней мере, не обеспечивают выгоды, и часто являются большим сопротивлением, чем они стоят.