All indexes must be considered in context of the DML statements acting upon them.
Originally Posted by frank.svs
In my scenario, the second create index statement is giving me better performance.
As such, there are queries for which the reverse would be true.
Include columns are in the index leaf level only. The other columns are in the index B-Tree too. Essentially, this is just like the structure of a clustered index. The columns you specifiy for the index are in the B-Tree and the rest of the columns in your table appear only in the leaf pages.
Again, it depends on the query. You would have to tell me your context.
It also depends on the data. The two indexes are not so different if there are only 20 rows and c4 and c5 are bits. They are worlds apart if c4 and c5 are CHAR(200) and there are 5 billion rows.
Post some more information (precise DDL, row counts, the queries you are seeing the improvement with) otherwise the best I can do is "the B-Tree is (most probably) smaller". I would bet if you SET STATISTICS IO ON you will see less IO for your second index.
It could also be that the clustered index on the table is a pain to navigate (could be defined on several large columns).
What the include statement does is includes the given columns at the lowest level of the index tree. This means these columns do not have to get sorted during inserts/updates, and do not clutter up the root pages of the index. It also means that the index has a greater chance of being used as a "covering index", so the Query Optimizer does not have to include a step to go back to the whole table to get the affected rows.
I won't bother to give any sort of argument as to whether it should work or not. You seem to have an example, yourself.