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/']
First of all - if you search for `RECORD_TYPE_CODE` you should make sure to have an index on that column. 

Besides that mainly two things:

* **don't** use `SELECT *` - that'll always have to go back to the clustered index to get the full data page; use a `SELECT` that explicitly specifies which columns to use
* if ever possible, try to find a way to have a **covering** nonclustered index, e.g. an index that contains all the columns needed to satisfy the query

If you have such a covering nonclustered index, then the query optimizer will most likely use that covering index (instead of the actual clustered index which is the full table data) to fetch the results
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)