Full-Text Search (SQL Server)

                     Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. Each full-text index indexes one or more columns from the table, and each column can use a specific language

.

Difference Between ‘Like’ and ‘Full-Text Search’

1.      You cannot use the LIKE predicate to query formatted binary data.

2.      LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data

Architecture of Full-Text Search

Prerequisites for Full-Text Search

1.  Configure database for Full-Text Search

2. Must be created Unique/Primary Key On that table.

3. Must be created Full text index on the column which we want to be using for Full-Text search.

Example:

1.CREATE TABLE BOOKS(BOOK_ID INT CONSTRAINT PK PRIMARY KEY , BOOK_TYPE VARCHAR(200))

2.CREATE FULLTEXT CATALOG ft AS DEFAULT;

3.CREATE FULLTEXT INDEX ON BOOKS(BOOK_TYPE)

   KEY INDEX pk

   WITH STOPLIST = SYSTEM;

4.INSERT INTO BOOKS VALUES(2,'.NET BOOK')

5.INSERT INTO BOOKS VALUES(2,'.JAVA BOOK')

6.SELECT BOOK_TYPE

FROM BOOKS WHERE CONTAINS(BOOK_NAME, 'JAVA')

Result:

Book_type

Java