Why SQL server is not using defined index ?

See more:

SQL-Server

I have a table containing around 400,000 records. There is a primary key and I created index on one column. I tried running query and checked execution plan but MSSQL Server is not using defined index. I tried few variance of creating index and only time it uses index is when I define it using INCLUDE clause with rest of the columns. Why is this the case ? Am I missing something ? Query is being fired from C# application so I don't want to specify use index hint.

CREATE TABLE [dbo].[LockJobs](
	[RecordId] [int] IDENTITY(1,1) NOT NULL,
	[SystemNo] [varchar](50) NOT NULL,
	[JobNo] [varchar](50) NOT NULL,
	[KeyNo] [varchar](50) NULL,
	[PickSlipNo] [varchar](50) NULL,
	[DebtorAcc] [varchar](50) NULL,
	[DateTimeCreated] [datetime] NULL,
 CONSTRAINT [PK_LockJobs_1] PRIMARY KEY CLUSTERED 
(
	[RecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

RecordId is primary key so there is a clustered index on it. I have created another index on System No. From data point of view a system number can have many Job Numbers. The SQL query I am trying is as below.

select * from LockJobs where SystemNo='S60175' ORDER  BY JobNo  -- ( No of records returned = 27000 ) 


1> create  index INDX_LockJob on  LockJobs(SystemNo) -- SQL does not use it when running above query. 

2> create  index INDX_LockJob on  LockJobs(SystemNo) INCLUDE([JobNo])-- SQL does not use it when running above query. 

3>create  index INDX_LockJob on  LockJobs(SystemNo) INCLUDE([RecordId],[JobNo],[KeyNo],[PickSlipNo],[DebtorAcc],[DateTimeCreated]) -- SQL uses the created index.

What I have tried:

Tried creating various variant of index. Only time it works is when using INCLUDE with all the columns. If index is storing this at a leaf level then it will make updating and inserting records in table expensive. Looking for explanation on what I am doing wrong.