Jeff Moden (2/27/2015)

---

ScottPletcher (2/27/2015)

---

Jeff Moden (2/27/2015)

---

It's also a bit odd for me to have you tell me not to be so concerned with the speed of imports when you, yourself, have posted just such a concern at http://www.sqlservercentral.com/Forums/FindPost1664429.aspx. 😉

The concern there is the overhead adding by forcing the varchar(50) to be in a separate nonclustered index, thus doubling I/O for every INSERT.

And, as I noted above, forcing the varchar(50) to a separate nonclus index also doubles the I/O on every lookup. That's not one I/O being doubled, btw, but one for each level of index, typically three for millions of rows.

The INSERT speed will also be slower with an additional index to load, particularly since that index will fragment.

The table with identity will not have 0% freespace either. And all INSERTs have to be serialized, so that identity numbers can be sequentially assigned.

For this specific table, since SELECTs always specify specific key value(s), why force additional index(es), with subsequent maintenance, adjusting to keep the index covering, etc.? It just doesn't make sense at all from a performance perspective. The only reason seems to be to avoid a varchar(50) as a clustering key: that's just wrong since the varchar(50) is the only index needed.

Imagine an "out-of-insert-order" index (clustered or otherwise) that creates a page split for every insert. On large tables, it can very quickly become a serious problem especially if that index is the clustered index.