How can I reduce the clustered index scan cost of below mentioned query

```
DECLARE @PARAMVAL varchar(3)

set @PARAMVAL = 'CTD'
select * from MASTER_RECORD_TYPE where RECORD_TYPE_CODE=@PARAMVAL

```

if I run the above query it was showing index scan 99 % 

Please find here below my table particularities:

![enter image description here](https://i.stack.imgur.com/BvW0J.jpg)

here below i have pasted my index for the table:

```
CREATE TABLE [dbo].[MASTER_RECORD_TYPE] ADD  CONSTRAINT [PK_MASTER_REPORD_TYPE] PRIMARY KEY CLUSTERED 
(
    [Record_Type_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

```

kindly advise how can i reduce index scan cost?
['https://Stackoverflow.com/questions/11826441', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1494292/']
You need to try and use a covered index. But the problem you're going to have is that you're using `SELECT *`. Do you really need the entire record?

Either way, add `RECORD_TYPE_CODE` to another index and it will help with the query because at least that field can be read off of an index page.
In your query, you have used column `RECORD_TYPE_CODE` which is not part of `clustered index` and also not included in any `non-clustered index` too. So SQL Optimizer will decide to scan the clustered index to do comparison of where clause predicate.

[Why is there a scan on my clustered index?](https://stackoverflow.com/questions/1161303/why-is-there-a-scan-on-my-clustered-index)