• ChrisM@Work (8/17/2016)


    There is a use case for having an ordinary index with the same leading edge column as the clustered index. If your table is very wide (meaning less rows per page), a narrow ordinary index can be significantly more performant. Always test - if your narrow index doesn't cover, you've blown it.

    Actually, it can be more than slightly beneficial. Creating an NCI on the CI key column will be a serious benefit to a range scan. Instead of reading the entire CI, it can do a seek against the NCI and the reads plummet. Like you said though - if it isn't covering, you're going to take a hit no matter which choice the optimizer makes.