NonClustered Index not being Used

I have a table (created below) that contains 321 rows.

I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a single row is returned as expected.

Why does it do the scan rather than use the NonClustered Index? Is it because the table contains only 321 rows?

CREATE TABLE dbo.TestIndexSample
(
    Code char(4) NOT NULL,
    Name nvarchar(200) NOT NULL,
    ModifiedDate datetime NOT NULL CONSTRAINT [DF_TestIndexSample_ModifiedDate] DEFAULT GETDATE(),
    CONSTRAINT [PK_TestIndexSample_Code] PRIMARY KEY CLUSTERED(Code)
);
GO
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name  
    ON dbo.TestIndexSample(Name);   
GO

INSERT INTO dbo.TestIndexSample(Code, Name)
select CodeName, FullName 
from dbo.SourceTest 
GO

SELECT * FROM dbo.TestIndexSample

SELECT * FROM dbo.TestIndexSample where Code = 'X132EY'

SELECT * FROM dbo.TestIndexSample where Name = 'User A'

You can force SQL Server to use the nonclustered index:

SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample WITH(INDEX (IX_TestIndexSample_Name))
WHERE  Name = 'NAME10';

dbfiddle here, plan here

Without the hint the query optimizer considers the plan with the index more costly for this small amount of data, but you can get this plan by increasing the number of rows in your table. I managed to achieve it with 600 rows:

SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample 
WHERE  Name = 'NAME10';

dbfiddle here

If you want to get just the Index Seek, your query should return only Name, so that data is pulled only from the index.

SELECT Name 
FROM   dbo.TestIndexSample  
WHERE  Name = 'NAME10';

dbfiddle here, plan here

But if other columns need to be returned too, you could use a covering index:

CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name  
    ON dbo.TestIndexSample(Name) INCLUDE (Code, ModifiedDate);

Obviously the index size will increase, but nothing important for this number of rows.

Now your current query will use an Index Seek:

SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample 
WHERE  Name = 'NAME10';

dbfiddle here, plan here

StackExchange Database Administrators Q#199487, answer score: 9