Performing search on SQL Server 2008 database

Question

We have built an ASP.NET application (with C#.net language) and hosted on Windows Server 2003 Operating System.

The database is SQL Server 2008.

Now we need to do a search involving 4 tables and one of the column of table is varchar (2400) and the other columns are of normal lengths (E.g. Varchar(50) etc.). This search gets fired whenever an user enters a keyword in the search box and presses enter key. The number of searchable records in future could be in lakhs.

Would you please let me know the best method of searching these tables?

If would be great if you could provide a reference document or web reference (If needed).

Many Thanks, Regards, Venkat

Answer 1

To search text columns you should use Full-text search

More resources: Understanding SQL Server Full-Text Indexing, SQL Server Full Text Search: Language Features and Full Text Search Step by Step Tutorial.

However it depends on the type of queries you make. Regular search using LIKE can be also acceptable.