Should I use a composite or single-column index?

I have the following columns in my database table (Medicines).

ID bigint, 
MedicineName nvarchar(50), 
BrandName nvarchar(50), 
MedicineCode nvarchar(20),
and price,quantity.

I am making a stored procedure with this query:

create proc searchmedicine
@name nvarchar(50)=null,@brand nvarchar(50)=null, @code nvarchar(20)=null
as
select * from Medicines
where MedicineName= Case WHEN @name IS NOT NULL THEN @name ELSE MedicineName END
AND BrandName=Case WHEN @brand IS NOT NULL THEN @brand ELSE BrandName END
AND MedicineCode=Case WHEN @code IS NOT NULL THEN @code ELSE MedicineCode END

Now I am confused which non-clustered index is more suitable to help optimize the query: composite or single column?

Using SELECT * is bad practice especially in a stored procedure. Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.

As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied. However I would have a clustered index on ID and 1 non clustered index on MedicineName, BrnadName, MedicineCode since it is them 3 columns you are selecting from.

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineBrands ON Medicines (MedicineName, BrandName, MedicineCode)

I'd then personally include the execution plan and see how your stored procedure performs compared to having the following indexes:

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineCode ON Medicines (MedicineCode)

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineName ON Medicines (MedicineName)

CREATE NONCLUSTERED INDEX IX_Medicines_BrandName ON Medicines (BrandName)

StackExchange Database Administrators Q#42585, answer score: 4