# 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?

## migrated from stackoverflow.comMay 20 '13 at 12:39

This question came from our site for professional and enthusiast programmers.

• Not related to indexes, but your proc has an issue. Since none of your input parameters have default values, they can't be null. That makes your select query overengineered. – Dan Bracuk May 20 '13 at 12:07
• sorry i forgot to write default values. in actual there are default values. – aliraza iftikhar May 20 '13 at 12:13
• I would be surprised if there were any indexes that could be used by that stored procedure. – Neil May 20 '13 at 12:39
• @Dan the default values just specify what happens if a parameter is not specified. You can still explicitly pass NULL even if no defaults are supplied. – Aaron Bertrand May 20 '13 at 13:32

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)

• thanks for suggestion. i know select * is bad practice but it is my requirement in this query i needed all the columns. i am just confused which index is suitable to make this query work efficiently. and i know execution plan can help me. but i am not familiar with execution plan hope so you can give me more better suggestion/solution. – aliraza iftikhar May 20 '13 at 12:03
• @alirazaiftikhar - it would be hard to determine which is best because we do not have your data. If you cannot provide the data then use the first nonclustered index I have provided and if it is efficient then use that, if not drop the index and split them up into 3 (as I have shown) and see if it is faster. – Darren May 20 '13 at 12:06
• @aliraza just because you "need all the columns" doesn't mean you should use SELECT *. Please read this in full. – Aaron Bertrand May 20 '13 at 13:34
• okay.then what should i do? – Ali Raza Iftikhar May 20 '13 at 13:48
• Name the columns. You can do this just as easily as you named them in the question above. – Aaron Bertrand May 20 '13 at 13:52

Try this one -

CREATE TABLE dbo.Medicines
(
ID BIGINT IDENTITY(1,1) PRIMARY KEY
, MedicineName nvarchar(50)
, BrandName nvarchar(50)
, MedicineCode nvarchar(20)
, Price DECIMAL(10,2)
, Quantity INT
)

CREATE NONCLUSTERED INDEX [ind_Medicines] ON [dbo].[Medicines]
(
[MedicineName] ASC,
[BrandName] ASC,
[MedicineCode] ASC
)
INCLUDE ([Price], [Quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.usp_SearchMedicine
@name NVARCHAR(50)
, @brand NVARCHAR(50)
, @code NVARCHAR(20)

AS BEGIN

SELECT
m.MedicineName
, m.BrandName
, m.MedicineCode
, m.Price
, m.Quantity
FROM dbo.Medicines m
WHERE m.MedicineName = ISNULL(@name, m.MedicineName)
AND m.BrandName = ISNULL(@brand, m.BrandName)
AND m.MedicineCode= ISNULL(@code, m.MedicineCode)

END

• @devart: seems like you are also suggesting that composite is more better, include column is a good practice or bad? – aliraza iftikhar May 20 '13 at 12:11
• I think, if filtration goes on 3 columns - the composite index is more profitable. – Devart May 20 '13 at 12:15
• "include column" - in your case it will be okay. – Devart May 20 '13 at 12:19