How to create non-clustered index for OR Operation

Question

Requirements: table Product has Name and Barcode

I want to create a non-clustered index to search with Name or Barcode

Query sample

DECLARE @Filter NVARCHAR(100) = NULL

SET @Filter = '%' + ISNULL(@Filter, '') + '%'

SELECT *
FROM Product
WHERE Name LIKE @Filter
OR Barcode LIKE @Filter

Please help me provide any the solution as separate to two indexes for name and barcode or using one index include name and barcode

Answer 1

You have a couple of issues at hand here. First off, even if an index existed on Name or Barcode your filter expression would not be able to benefit from the index (in the traditional sense for adding an index) because the expression is not sargable. Brent Ozar has a great article explaining Why %string% Is Slow

Second, you cannot create a single index to cover filters on two separate columns where the filters on each column are independent. Meaning you are either using two separate filters (Ex: your OP), or your query only includes a filter for Name = 'NameValue' or vice versa. A query that has a where clause of:

WHERE 
    Name = 'NameValue' 
    OR 
    Barcode = 'BarcodeValue'

Would only be able to seek an index for both filter expressions if separate indexes existed where Name was the first listed column and Barcode was the first listed column.

An index containing two columns is meant to primarily serve the purpose of filters that use both columns as part of the filter expression. Ex: Name = 'NameValue' AND Barcode = 'BarcodeValue'. It is also very important to think about the ordinal position of each column within the index. For example, lets say you create this index: CREATE NONCLUSTERED INDEX NCIX_Product_Name_Barcode ON Product (Name,Barcode); A query with the filter expression Name = 'NameValue' can still seek this index because Name is the first column in the index, but a query with the filter expression Barcode = 'BarcodeValue' cannot.

Before making any long term decisions on index design, you should first familiarize yourself with the guidelines published by Microsoft in General Index Design Guidelines.

Lastly, if you truly need to search the Name or Barcode for string matches, you should look in to Microsoft's documentation on full text indexes which is likely going to be your best solution for indexed searches of this manner.