NonClustered Index not being Used

Question

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'

Answer 1

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

Answer 2

Is it because the table contains only 321 rows?

Yes it should be the cause.

How many pages does your table have? It is very probable it has only one data page, so clustered index scan means that server can read only 2 pages to make this work while doing lookup means 2 pages of nonclustered index + 2 pages of clustered index.

You can see page count using this code:

select index_id, total_pages
from sys.allocation_units au join sys.partitions p
        on au.container_id = p.hobt_id
where p.object_id = object_id('dbo.TestIndexSample');

Here index_id = 1 corresponds to clustered and index_id = 2 to nonclustered index.