|
if you have source а yaf - there is a file
\yafsrc\YetAnotherForum.NET\install\mssql\fulltext.sql
If you dont - here the entire file content :
[code]
-- Enables FULLTEXT support for YAF
-- Must be MANUALLY run against the YAF DB
if (select fulltextserviceproperty('IsFulltextInstalled'))=1 and (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) = 1
BEGIN
if not exists (select * from dbo.sysfulltextcatalogs where name = N'YafSearch')
BEGIN
EXEC sp_fulltext_catalog N'YafSearch', N'create'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'create', N'YafSearch', N'PK_yaf_Message'
EXEC sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Message]', N'Message', N'add'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'activate'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'Start_change_tracking'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'Start_background_updateindex'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'create', N'YafSearch', N'PK_yaf_Topic'
EXEC sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Topic]', N'Topic', N'add'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'activate'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'Start_change_tracking'
EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'Start_background_updateindex'
-- enable in yaf_Registry as a default
IF EXISTS ( SELECT 1 FROM [{databaseOwner}].[{objectQualifier}Registry] where [Name] = N'usefulltextsearch' )
UPDATE [{databaseOwner}].[{objectQualifier}Registry] SET [Value] = '1' WHERE [Name] = N'usefulltextsearch'
ELSE
INSERT INTO [{databaseOwner}].[{objectQualifier}Registry] ([Name],[Value],[BoardID]) VALUES (N'usefulltextsearch','1',NULL);
END
END
GO
[/code]
I think its pretty self-explained.
You can read about benefits here http://msdn.microsoft.com/en-us/library/ms345119(SQL.90).aspx
Here the short quote from link above:
[quote]
Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language.
The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query (for example, '%cencini%') against millions of rows of text data can take minutes to return; whereas a full-text query (for 'cencini') can take only seconds or less against the same data, depending on the number of rows that are returned.
[/quote]
|