I would try to avoid OR operators in this scenario. I actually mention https://brendan-mccaffrey.com/2021/12/20/stop-using-the-or-operator/ in a recent blog post. While that post was about updates, the concept should translate well here. Basically, using the OR operator will make indexing challenging. You'll need to know which column needs to go first in any index you create, and hope it's SELECTIVE and will always have input.
Also, you'll want to avoid the leading '%' in your search string, as no index will be able to help in that case. The easiest way to visualize why no index will help here it to think of a phone book. I know, many people probably don't know what these are any more. Now, picture trying to look up a phone number where you're missing the first letter of someone's last name. If you knew the name started with an 'M', you might just flip to the Ms. Then maybe flip a few more pages if you land on 'Ma', but need 'Mc". In either case, you'll get there fast. But without the first letter, you'll have to look at every record, front to back, of the entire phone book to find a name like '%cCaffrey, for example.
Since your concatenation between first name and last name, you'll probably want to consider an index on a computed column, or an indexed view. You may need to experiment to see which one works best in your scenario. For example, here would be a computed column with an index.
ALTER TABLE Waiver ADD FullName AS CONCAT(FirstName, ' ', LastName)) PERSISTED
GO
CREATE NONCLUSTERED INDEX IX_FullName ON dbo.Waiver (FullName)
GO
If you were to continue concatenate those columns in your WHERE clause, they'll produce a full table scan every time. SQL Server can't know two columns equal your condition until it concatenates them for every row in the table. These issues might not end up being a problem at 30k rows now, but could be big problems as the row counts grow.
If you must using a generic search box, I would try writing your query somewhat like this. In this case, you could create a unique index for each column. You're using SELECT *
, so you'll always be doing KEY LOOKUPS anyways.
DECLARE @search VarChar(100)
SET @search = 'Smith'
SELECT *
FROM Waiver
WHERE LastName LIKE @search + '%'
UNION
SELECT *
FROM Waiver
WHERE FullName LIKE @search + '%'
UNION
SELECT *
FROM Waiver
WHERE Email LIKE @search + '%'
UNION
SELECT *
FROM Waiver
WHERE DriversLicense LIKE @search + '%'
I removed FirstName from the search in the example, because it would already be met in the FullName search.
You would still need to evaluate additional indexes on common search terms.
With that said, I would advise you to see if can create several search boxes on the webpage, one for each search condition.