One of the tips in http://www.sql-server-performance.com/transact_sql_where.asp
If your application performs many wildcard (LIKE %) text searches on CHAR or VARCHAR columns, consider using SQL Server's full-text search option. The Search Service can significantly speed up wildcard searches of text stored in a database. [7.0, 2000] Updated 10-16-2006
I am not quite understand that . Will you please give me some example ? Thx.