Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

Users expect every application to have a built-in blazing-fast search functionality.  To pull that off, developers build search queries that let users enter a string, and we ask SQL Server or any other DBMS system to find matches.  For example, say any users need to find some drink. This string, put percent signs on either side at the background first, and then search for that string in the product names:

SELECT *
FROM AdventureWorks.Production.Product
WHERE [Name] LIKE '%drink%'

This works great only when if there were less number of records, but when the records become high lets say in millions, server starts slowing down and response time becomes so waiting.

Okay, have you ever wondered why there is slowness while pulling off the record from the backend? Probably you would like to check the behavior through execution plan.

1. SQL optimizer always use Index Scan rather than Seek when Wildcard gets introduced.
2. Even if the particular column has index on the it, it wont do the seek.
3. To get rid off this problem and want SQL optimizer should use Seek, you can put % wildcard end of the string example:-

SELECT *
FROM AdventureWorks.Production.Product
WHERE [Name] LIKE 'drink%'

Hope this helps!