Full Text Search/Indexing
if you are implementing a search on your web application.and you want to query or search but not the physical path or pages, you want to search what is contained or data on your database.
then this fulltext search feature is already there since sql server 7 i think.
So instead you do a search with " Like %something%" , which will trigger the whole table scan for that . imagine you have many users and dependful on search. ex: like what i'm building now, public tracker site. it will bloat the server memory just to serve the search
Architectural:http://msdn.microsoft.com/en-us/library/ms142541.aspx
The full text run on another services, which will Crawl your table and build your indexes. When a search is there it serves from the indexes.
there are also few word like contains, freetext, containstable,freetexttable.
The table ones you can inner join the result with.
and also if you want to make a query that will search 'LIKE %something%'
you should declare variable before passing to contains(param) it will make error believe me.
ex:
DECLARE @temp varchar(250)
set @temp= '"*' + @word + '*"'
SELECT T.ID,T.Name
FROM
Something T
INNER JOIN
CONTAINSTABLE(Torrent, *,@temp ) AS SomethingResults
ON T.ID= SomethingResults
.[KEY]
LEFT JOIN
Type on T.TypeID=[Type].TypeID
To check population Status:
--check populate status
/*0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
*/
SELECT fulltextcatalogproperty('TradixFullTEXT', 'PopulateStatus');
--check how many item currently on index
SELECT fulltextcatalogproperty('TradixFullTEXT', 'ItemCount');
To force update population from query :
ALTER FULLTEXT INDEX ON Table_Name SET CHANGE_TRACKING Manual
ALTER FULLTEXT INDEX ON Table_Name START UPDATE POPULATION
Enable Fulltext on DB
EXEC sp_fulltext_database 'enable'
Check fullText is enabled or not
SELECT DATABASEPROPERTY('databasename', 'IsFullTextEnabled');
Check full text index Status
select * from sys.fulltext_indexes Table_Name
Once you 've added it . you can scheduled it as you wish . like occuring every day, at what time, every hour and so on
just right click on the table->fulltextindex->Schedule. Than change what ever we like it's adding job to sql server agent . and definitely this feature is not included on sql express.. because express does not have sql server agent.