Hi...,

I again got confused on Index concepts. I have seen one article for Full Text Search and got confused regarding the Like keyword.

Can anyone please help me to understand clearly the difference between Full Text Search Vs. Like Keyword?

Once again also I want to know how I can use the wildcard search in Full Text Search Index?

UR's

Radhika.

Dear Radhika,

Check this nice article : http://technet.microsoft.com/en-us/library/ms345119(SQL.90).aspx

Yes, You can do a wildcard search too in full text search.

Query for you:

USE DEMO

--

CREATE TABLE Test_LikeVsFullText

(

Tid INT IDENTITY(1,1),

TName NVARCHAR(50)

)

--

CREATE UNIQUE CLUSTERED INDEX CL_TID ON Test_LikeVsFullText (TID)

--

INSERT INTO Test_LikeVsFullText

SELECT 'TestData'

UNION ALL

SELECT 'TestData1'

UNION ALL

SELECT 'TestData2'

--

SELECT * FROM Test_LikeVsFullText WHERE TName Like '%D%'

--

USE DEMO

EXEC sp_fulltext_database 'enable'

--

CREATE FULLTEXT CATALOG Test WITH ACCENT_SENSITIVITY=OFF AS DEFAULT

--

CREATE FULLTEXT INDEX ON Test_LikeVsFullText(TName)

KEY INDEX CL_TID WITH CHANGE_TRACKING MANUAL

--

SELECT * FROM Test_LikeVsFullText WHERE CONTAINS(TName, '"TestD*"')

--

DROP TABLE Test_LikeVsFullText

You have to specify the format for the search and "*" for the search.

NOTE: We use full text Index for Text/Image Datatype Column. I have implemented in NVARCHAR Column for only Example.

Hope it will help you.

Thanks,

Sandy.

Like is used to find strings in string data fields, like a varchar, char, etc.  It is good for things like that.  However, the database has to load every record to then do a comparison on that field.  There is no index on it to tell it exactly what records to pull.  It must pull all to look for the data.

With Full Text Search, actual indexes are created on the fields for all the key words.  It knows to disregard words like "the", "and", etc.  Further, indexes are created on BLOB fields and their contents, such as excel spreadsheets, Word documents, etc.  Special select statements allow you to search for words in those files, also.  Of course, there is some overhead, and you have to determine if the indexes can be created for the file at insert, or if you want to manually run the indexes at certain intervals.  It can also do fuzzy lookups, and other neat things.  You can also use a Thesaurus feature, which also allows you to add words.  It is very powerful.

If you are building any sort of document management system, I would definitely look into Full Text Search.  It is much more powerful than a simple "LIKE" statement on a string field.