why fulltext search used?
-
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, ‘%microsoft%’) against millions of rows of text data can take minutes to return; whereas a full-text query (for ‘microsoft’) can take only seconds or less against the same data, depending on the number of rows that are returned.
Keyword Used In FullText Search
1)FreeText 2)FreeTextTable 3)Contains 4) ContainsTable
1)FreeText
2)FreeTextTable
-
Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.
3)Contains
- A word or phrase.
- The prefix of a word or phrase.
- A word near another word.
- A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
- A word that is a synonym of another word using thesaurus (for example, the word metal can have synonyms such as aluminum and steel).
4)ContainsTable
- Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
- Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.
I am use the sql server version Standard Edition.
Steps:
1) Enabled the fulltext on the database using the sp.
EXEC sp_fulltext_database 'enable'
2) Create Table in Database
CREATE TABLE [dbo].[UserDetail](
[UserDetailId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NULL,
[Address1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_UserDetail] PRIMARY KEY CLUSTERED
(
[UserDetailId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
3) Insert Record Into Table
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(1,’nava india’,'programming’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(3,’nava america’,'Developing’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(5,’juna india’,'Testing ‘)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(6,’juna america’,'QA’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(8,’india’,'PM’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(3,’japan’,'Developing’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(3,’nava japan’,'Developing’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(1,’juna japan’,'programming’)
INSERT INTO [UserDetail]
([UserId],[Address1],[Address2])
VALUES
(1,’america nava’,'programming’)
4) Enabled Full Text in Table
Right Click On Table
* Select Full Text Index
* Define Full Text Index
Click Next –> Next
–here you select Field where you apply full text search field.
select both the field and press Next.
In Next Select automatic and Press Next.
In Next Select Full Text Catalog and Press Next.
Press Next –> Next –> Press Finish Button
5) Query Using FullText Search Keyword
5.1) FreeText
Search From Address1 Field where keyword like nava,juna,america.
Search From Address1,Address2 Field where keyword like nava,india,QA
5.2 ) FreeTextTable
- SELECT * FROM UserDetail AS FT_TBL INNER JOIN FREETEXTTABLE(UserDetail,Address1,‘nava juna’) AS KEY_TBL
ON FT_TBL.UserDetailId = KEY_TBL.[KEY];
–TableName -UserDetail
–Column Name – Address1
–Search Keyword -words
Search From Address1 field where keyword like nava juna
-
SELECT * FROM UserDetail AS FT_TBL INNER JOIN FREETEXTTABLE(UserDetail,*,
‘nava PM’) AS KEY_TBL ON FT_TBL.UserDetailId = KEY_TBL.[KEY];
Search From Address1,Address2 field where keyword like nava,PM
5.3)CONTAINS
search from Address1 where keyword like nava
select * from userdetail where Contains(address1,'nava juna')
you can get the error if you write this with contain keyword.
Search From Address1 where keyword like nava or india
- select * from userdetail where Contains(address1,‘”na*”‘)
- select*fromuserdetail where Contains(*,‘”nava” or “india” or “PM”‘)
Search From Address1,Address2 where keyword like nava or india or PM
- select * from userdetail where Contains(*,‘ISABOUT (nava weight (0.8))’)
Thanks.
Reference Site :
MSDN Help
http://msdn.microsoft.com/en-us/library/ms345119.aspx
