Sql-server – Non clustered index : performance issue

Generally, an index should be on

(equalitycol1, equalitycol2, ..., nonequalitycol1, , nonequalitycol2, ...)
INCLUDE
(outputonlycol1, outputonlycol2, ...)

Simply (ignoring ranges) this breaks down into

• equalitycol: WHERE equalitycol = something. Most selective first. SARGable. Seeks.
• nonequalitycol: WHERE nonequalitycol <> something. Residual filters
• outputonlycol: SELECT outputonlycol. No filtering or ordering

ORDER BY and GROUP BY columns will be equality or non-equality columns.

In this case...

Query 1 doesn't match the index at all, really, so most likely won't use the index. However, the optimiser may decide it's cheaper to use the index then use other table access such as a table scan or a RID/bookmark lookup.

For query 2, the optimiser may decide to use the index because col1 would be an equality match. And then use the unsorted col5 from the INCLUDE. YMMV, but there is a higher chance.

For the WHERE clause order, no: the optimiser works it out. Two indexes (col1, col2) and (col2, col1) are different though. Note (col1, col2) will be contained by (col1, col2, col3, col4) though so the first isn't needed.

The blog post you reference also indicates how you could have answered this yourself.

If you execute

SET SHOWPLAN_TEXT ON;
GO
UPDATE T SET A = 19;

The plan looks like

  |--Clustered Index Update(OBJECT:([AdventureWorks2008].[dbo].[T].[TPK]), OBJECT:([AdventureWorks2008].[dbo].[T].[TF]), SET:([AdventureWorks2008].[dbo].[T].[A] = [@1]))
       |--Compute Scalar(DEFINE:([Expr1009]=[Expr1009]))
            |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN CASE WHEN [AdventureWorks2008].[dbo].[T].[A] = [@1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END))
                 |--Top(ROWCOUNT est 0)
                      |--Index Scan(OBJECT:([AdventureWorks2008].[dbo].[T].[TF]), ORDERED FORWARD)

showing a per-row / narrow plan where the index TF is listed as one of the objects updated.