sql server - Full text search vs LIKE -

my question using fulltext.as know queries begin % never use index :

select * customer name %username% 

if use fulltext query can ı take better performance? can sql server use fulltext index advantages queries %username%?

short answer

there no efficient way perform infix searches in sql server, neither using like on indexed column, or fulltext index.

long answer

in general case, there no fulltext equivalent operator. while works on string of characters , can perform arbitrary wildcard matches against inside target, design fulltext operates upon whole words/terms only. (this slight simplification purpose of answer.)

sql server fulltext support subset of prefix term operator. docs (http://msdn.microsoft.com/en-us/library/ms187787.aspx):

select name production.product contains(name, ' "chain*" '); 

would return products named chainsaw, chainmail, etc. functionally, doesn't gain on standard like operator (like 'chain%'), , long column indexed, using prefixed search should give acceptable performance.

the operator allows put wildcard anywhere, instance like '%chain', , mentioned prevents index being used. fulltext, asterisk can appear @ end of query term, of no you.

using like, is possible perform efficient postfix searches creating new column, setting value reverse target column, , indexing it. can query follows:

select name production.product name_reversed 'niahc%'; /* "chain" backwards */ 

which returns products names ending "chain".

i suppose combine prefix , reversed postfix hack:

select name production.product name 'chain%' , name_reversed 'niahc%'; 

which implements (potentially) indexed infix search, it's not particularly pretty (and i've never tested see if query optimizer use both indexes in plan).