c# - SQL Server and performance for dynamic searches -

i wondering best practices making query in sql dynamic value, lets have value(nvarchar(max))

value: "912345678"

select * alldata number '%912345678%'  

value: "michael"

select * alldata name '%michael%'  

value: "street number 10"

select * alldata address '%street number 10%'  

this approuches bit slow since searching number has 9 digits faster without % this

select * alldata number '912345678'  

i use edmx make connection external database in c#, this:

var result = edmxentity.entities.where(x =>  (sqlfunctions.patindex("%" + value.tolower() +"%", x.name.tostring().tolower()) > 0)  || (sqlfunctions.patindex("%" + value.tolower() +"%", x.number.tostring().tolower()) > 0) || (sqlfunctions.patindex("%" + value.tolower() +"%", x.address.tostring().tolower()) > 0)).take(50).tolist(); 

how can increase performance?

wildcard searches these on varchar/nvarchar fields going to iterate on every character, more or less, records meet critieria.

a great (and fast!) option these kinds of searches to:

1. make fulltext catalog store fulltext indexes.
2. put fulltext index on columns in each table need search.
3. use contains keyword when search rather wildcards.

you mentioned looking credible sources, here read.