How Does SQL Server Choose Indexes to Use
In my limited experimentation:


When the query has to perform a scan, SQL Server does the work to find the narrowest index every time.
When the query is expected to perform a seek, it doesn't care about index width, it just uses the last covering index created.


This kind of makes sense. If you're going to add rules to search for the perfect index in either of these cases, you should do it when it's going to save you the most. On a seek the effort spent in finding a better index when it won't really matter in terms of savings for all rows as opposed to per row is far less likely to be worth it. "Last in" is maybe intentional, with the assumption that the last covering index you created is probably the best one you've created, or it may just be arbitrary and coincidental (like the order of columns in a missing index recommendation).

Proof (well, sort of proof)

My test setup was pretty simple:

CREATE TABLE dbo.t1
(
  id int IDENTITY(1,1) PRIMARY KEY,
  sn1 sysname, tn1 sysname, cn1 sysname, typ1 sysname,
  sn2 sysname, tn2 sysname, cn2 sysname, typ2 sysname,
  sn3 sysname, tn3 sysname, cn3 sysname, typ3 sysname,
  sn4 sysname, tn4 sysname, cn4 sysname, typ4 sysname
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.t1
(
  sn1,tn1,cn1,typ1,sn2,tn2,cn2,typ2,
  sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4
)
SELECT s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name
FROM sys.schemas AS s
CROSS JOIN sys.objects AS t
INNER JOIN sys.all_columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.types AS typ
ON c.user_type_id = typ.user_type_id;
GO

CREATE VIEW dbo.v1
AS
  SELECT id,sn1,tn1
  FROM dbo.t1;
GO


This put 13,260 rows into my table (your results will vary). Then I repeatedly created the same three indexes, in different order:

-- widest first
CREATE INDEX ix_wide ON dbo.t1(sn1) 
  INCLUDE(tn1,cn1,typ1,sn2,tn2,cn2,typ2,sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4);
GO
CREATE INDEX ix_mid ON dbo.t1(sn1) INCLUDE(tn1,cn1,sn2,tn2,cn2,typ2,sn3,tn3);
GO
CREATE INDEX ix_small ON dbo.t1(sn1) INCLUDE(tn1,cn1);
GO

-- widest last = ix_small then ix_mid then ix_wide
-- middle1 = ix_mid then ix_wide then ix_small
-- middle2 = ix_small then ix_wide then ix_mid


Then in each of those four cases I ran these two queries and investigated the plans:

DBCC FREEPROCCACHE;
GO
SELECT id,sn1,tn1 FROM dbo.v1; -- scan
GO
SELECT id,sn1,tn1 FROM dbo.v1 WHERE sn1 LIKE N'q%'; -- seek


Results:

         widest first  widest last  middle1   middle2
-------  ------------  -----------  --------  --------
   scan    ix_small      ix_small   ix_small  ix_small
   seek    ix_small      ix_wide    ix_small  ix_mid


The scan always chose the narrowest index. The seek always chose the index that was created last (since all cover). I didn't extend the tests to also include a non-covering index in the mix, because I don't suspect it will change this outcome.

Morals (well, sort of morals)

There are two morals here:


Create your widest indexes first if you want your narrowest indexes to be used in seeks when they can.
It's probably a narrow set of use cases where this optimization is worth it. Such as seeks that return a lot of rows and the wider covering index has very large columns that aren't in the narrower index. In these cases it might be worth explicitly specifying the index with a hint (and all the caveats that involves) rather than relying on the behavior I've observed here.


Probably some internals and heuristics I'm missing here that Paul will clarify. Also I tried to enable trace flag 302 to output index selection information, but this doesn't seem to work on modern versions of SQL Server.