What Index Will Work Best?

A common search technique for us is to pass a bunch of parameters to a query, but to leave most of the parameters empty. We only get records that match all parameters. So I can search for orders in CO with an email that includes yahoo.com. In some cases we search multiple fields for the same data and return the matching data. For example, there are 2 fields that could match @SchoolName. If either 1 matches that's okay.

How do I index this for the fastest results? (I'm new to SQL Server indexing). Do I want 1 index that includes all possible fields? Or individual indexes on each field? Or something else?

Here's a sample SPROC with a couple of parameters implemented in the WHERE clause:




Code Block
ALTER PROCEDURE [dbo].[Plan_Search]
(
@Ordernum VARCHAR(15),
@SchoolName VARCHAR(40),
@Zipcode VARCHAR(15),
@City VARCHAR(40),
@State VARCHAR(5),
@Contact VARCHAR(40),
@Ponumber VARCHAR(20),
@Quoteid VARCHAR(15),
@Phone VARCHAR(15),
@Email VARCHAR(75)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;

SELECT Ordernum,
( CASE WHEN Corrname LIKE '%' + @SchoolName + '%'
THEN Corrname
ELSE bilname
END ) AS School,
Corrcity AS City,
Corrstate AS State,
Corrzip AS Zipcode,
Daterecved,
Dateshiped,
Level,
Package,
Totplnrord,
Purchordno,
Checkindate
FROM PlannerOrders
WHERE ordernum LIKE '%' + @Ordernum + '%'
AND ( corrname LIKE '%' + @SchoolName + '%'
OR [bilschool] LIKE '%' + @SchoolName + '%'
)