title: SQL Server performs index scan when specifying columns rather than when using the * operator
body: <p>I'm trying to optimize a query where we find the most recent records in a large table using a simple <code>SELECT MAX(Timestamp) ... GROUP BY Id</code> and I've narrowed down the query to some behaviour I don't quite understand...</p>
  <p>When I run the below query everything works as I'd expect...</p>
  <pre><code>SET STATISTICS IO ON
  
  DECLARE @__dataSourceId nvarchar(12) = N'test'
  
  SELECT [b0].*
  FROM 
      (SELECT [b].[BondId]
       FROM [BondPrices] AS [b]
       WHERE [b].[DataSourceId] = @__dataSourceId 
         AND ([b].[BenchmarkBondId] = 'a' OR 
              [b].[BenchmarkBondId] = 'b'  OR 
              [b].[BenchmarkBondId] = 'c')
       GROUP BY [b].[BondId]) AS [t]
  INNER JOIN 
      [BondPrices] AS [b0] ON [t].[BondId] = [b0].[BondId]
  
  
  -- Results (232 records returned):
  -- Table 'BondPrices'. Scan count 39, logical reads 238
  </code></pre>
  <p><a href="https://i.stack.imgur.com/eLUEw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eLUEw.png" alt="Fast query" /></a></p>
  <p>However...</p>
  <p>If I change the above code from <code>SELECT [b0].*</code> to just <code>SELECT [b0].[BondId]</code> the query execution plan changes from an <strong>Index Seek</strong> to and <strong>Index Scan</strong> which strikes me as odd, and seems to have a detrimental impact on performance.</p>
  <pre><code>SET STATISTICS IO ON
  
  DECLARE @__dataSourceId nvarchar(12) = N'test'
  
  SELECT 
      [b0].[BondId] -- this is the only change
  FROM
      (SELECT [b].[BondId]
       FROM [BondPrices] AS [b]
       WHERE [b].[DataSourceId] = @__dataSourceId 
         AND ([b].[BenchmarkBondId] = 'a' OR 
              [b].[BenchmarkBondId] = 'b'  OR 
              [b].[BenchmarkBondId] = 'c')
       GROUP BY [b].[BondId]) AS [t]
  INNER JOIN 
      [BondPrices] AS [b0] ON [t].[BondId] = [b0].[BondId]
  
  
  -- Results (232 records returned):
  -- Table 'BondPrices'. Scan count 6, logical reads 4396
  </code></pre>
  <p><a href="https://i.stack.imgur.com/XQMr4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XQMr4.png" alt="Slow query" /></a></p>
  <p>Is there a reason why selecting just a single (indexed) column would cause the query plan to change so drastically?</p>
  <p>How could I ensure my query performs a Seek rather than a Scan in this instance?</p>
  <p><strong>Edit</strong>: as requested, the below are the indexes in question:</p>
  <pre><code>ADD CONSTRAINT [PK_BondPrices] 
      PRIMARY KEY CLUSTERED ([BondId] ASC, [DataSourceId] ASC, [Timestamp] ASC)
  
  CREATE NONCLUSTERED INDEX [IX_BondPrices_BondId_Timestamp] 
  ON [dbo].[BondPrices] ([BondId] ASC, [Timestamp] ASC)
  </code></pre>
  <p>The non-clustered index is an attempt at forcing the join to perform an index seek although it would be more optimal if the query could use the clustered index.</p>
  <p><strong>Edit</strong>: also including the tooltip containing the warning.</p>
  <p><a href="https://i.stack.imgur.com/NVGKl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NVGKl.png" alt="tooltip warning" /></a></p>