How To Enable Full Text Search With T-SQL?
Full Text Search is indexing and querying service used for searching strings in unstructured text. Full Text Search provides better performance than common search with LIKE keyword. T-SQL listing bellow enables full text search on Northwind database:
[ T-SQL ]
-- We'll use Northwind sample database to enable
-- Full Text Search feature using T-SQL code only
USE Northwind
GO
-- We need to enable full text search for Northwind database
-- We will do that with sp_fulltext_database procedure
EXEC sp_fulltext_database 'enable'
-- Create catalog
EXEC sp_fulltext_catalog 'NorthwindCatalog','create'
-- Add some indexes to database
EXEC sp_fulltext_table 'Customers', 'create', 'NorthwindCatalog', 'pk_customers'
EXEC sp_fulltext_table 'Orders', 'create', 'NorthwindCatalog', 'pk_orders'
-- add columns for searching to full text search index
EXEC sp_fulltext_column 'Customers', 'CompanyName', 'add'
EXEC sp_fulltext_column 'Customers', 'ContactName', 'add'
EXEC sp_fulltext_column 'Customers', 'Address', 'add'
EXEC sp_fulltext_column 'Customers', 'City', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipName', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipAddress', 'add'
EXEC sp_fulltext_column 'Orders', 'ShipCity', 'add'
-- Activate full text search indexes
EXEC sp_fulltext_table 'Customers','activate'
EXEC sp_fulltext_table 'Orders','activate'
-- start full population of catalog
EXEC sp_fulltext_catalog 'NorthwindCatalog', 'start_full'
Now you can perform search on indexed columns using CONTAINS, FREETEXT, CONTAINSTABLE or FREETEXTTABLE keywords. For example, let say I want to check all contacts where first name is Maria or Ana:
USE Northwind
GO
SELECT CustomerId, ContactName, CompanyName, Address, City
FROM Customers c INNER JOIN
CONTAINSTABLE(Customers, (ContactName), '"Maria" OR "Ana"') AS KEY_TBL
ON c.CustomerId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
This SQL query will return results as on image bellow:
Going professional with Search Control
Full Text Search on SQL Server is powerful tool, but in real world it is very demanding to create fast, correct and secure queries. If web or Windows application's users can search database, then you need to validate every user input, create correct SQL syntax and protect query from SQL injection attacks. Fortunately, there is pure .NET Control named Search Control that creates complex, fast and secure SQL for searching on the fly. Best of all, application or website users don't need to learn anything new because querying is identical to Google search, so if users know how to type search terms in Google search box, they are already expert for Search Control too :).
Related articles:
1. Ad Hoc Queries in ASP.NET
