1. Find the name of the primary key for the table that you want to index. My table name is "content" and the key looks like this: PK__fields__0xxxx
Create table content (
Pagename varchar(20) not null primary key,
URL archar(30) not null,
Description text null,
Keywords varchar(4000) null)
2. Create Full Text Index
CREATE FULLTEXT INDEX ON [content]KEY INDEX [PK__fields__0xxxx]WITH CHANGE_TRACKING AUTO
3. Add column(s) to the index
EXEC sp_fulltext_column 'fields', 'Pagename', 'add'
EXEC sp_fulltext_column 'fields', 'URL', 'add'
EXEC sp_fulltext_column 'fields', 'Description', 'add'
EXEC sp_fulltext_column 'fields', 'Keywords', 'add'
However its bad practice to enable all columns with full text indexing which significantly poses performance threat to the SQL Server. For more details you can google the best practices for full text indexing on the internet.
4. Activate index
EXEC sp_fulltext_table 'content', 'activate'
5. Start index population (if not started)
EXEC sp_fulltext_table 'content', 'start_full'
6. Enjoy full text search in your ASP.NET application
SELECT FREETEXTTABLE(content, description, @query, 100)
Below are some more examples you can check around.
Query 1 (FREETEXT)
SELECT * FROM content WHERE freetext(*,"home")
| |
| Result |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| home.asp | /home.asp | This is the home page | home, SQL |
This queries all full-text-enabled columns in the content table for the string "home."
Query 2 (FREETEXT)
SELECT * FROM content WHERE freetext(description,"Magazine")
| |
| Result |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| Pagetwo.asp | /page2/page2.asp | NT Magazine is great | second |
| Pagethree.asp | /page3/page3.asp | SQL Magazine is the greatest | third |
This only searches the Description column and returns all matches for the string "Magazine."
Query 3 (FREETEXT)
SELECT * FROM content WHERE freetext(description,"SQL Mag")
| |
| Result |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| Pagethree.asp | /page3/page3.asp | SQL Magazine is the greatest | third |
Although this appears to search on the string "SQL Mag," it actually searches on "SQL" or "Mag."
Query 4 (FREETEXT)
SELECT * FROM content WHERE freetext(description,"the")
Result
Server: Msg 7619, Level 16, State 1, Line 1
The query contains only ignored words; we've queried a noise word here. You'll find "the" in the noise words file at \MSSQL7\FTDATA\SQLSERVER\CONFIG.
Query 5 (CONTAINS)
SELECT * FROM content WHERE contains(*,"home")
| |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| home.asp | /home.asp | This is the home page | home, SQL |
Like the Freetext query, this searches all full-text-enabled columns for the keyword "home."
Query 6 (CONTAINS)
SELECT * FROM content WHERE contains(Description,' "Magaz*" ')
| |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| Pagetwo.asp | /page2/page2.asp | NT Magazine is great | second |
| Pagethree.asp | /page3/page3.asp | SQL Magazine is the greatest | third |
This statement queries the Description column for a word beginning with "Magaz." Note that the asterisk acts as a wildcard or placeholder, just as the percent sign (%) does with the LIKE keyword. (To make this work, you need to use single quotes on either side of the double quotes.)
Query 7 (CONTAINS)
SELECT * FROM content WHERE contains(Description,' "*azine" ')
| |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
This search yields no results. You can't use an asterisk as a placeholder for a prefix.
Query 8 (CONTAINS)
SELECT * FROM content WHERE contains(Description,' "Magazine" Or "Great" ')
| |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| Pagetwo.asp | /page2/page2.asp | NT Magazine is great | second |
| Pagethree.asp | /page3/page3.asp | SQL Magazine is the greatest | third |
This full-text scan uses OR so that you can search for "Magazine" or "Great"; it also works with AND and AND NOT. (Again, note the single quotes around the search criteria.)
Query 9 (CONTAINS)
SELECT * FROM content WHERE CONTAINS(description, 'NT NEAR great')
| |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| Pagetwo.asp | /page2/page2.asp | NT Magazine is great | second |
This search on the Description column finds all rows where "NT" is near "great".
Query 10 (CONTAINS)
SELECT * FROM content WHERE contains(description, ' formsof (inflectional, great) ')
| |
| Pagename | URL | Description | Keywords |
| -------- | --------- | --------------------- | --------- |
| Pagetwo.asp | /page2/page2.asp | NT Magazine is great | second |
| Pagethree.asp | /page3/page3.asp | SQL Magazine is the greatest | third |
This statement returns all results for "great," "greatest," "greater," and so on.
Sources: http://codeshard.blogspot.in/2011/10/godaddy-full-text-search-with-ms-sql.html
http://sqlmag.com/sql-server/sample-full-text-search-engine
