SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. Software developer Monica who helped with screenshots also informed that this feature works with RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions.

To create an Index, follow the steps:

  1. Create a Full-Text Catalog
  2. Create a Full-Text Index
  3. Populate the Index

1) Create a Full-Text Catalog


Full – Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

3) Populate the Index

As the Index Is created and populated, you can write the query and use in searching records on that table which provides better performance.

For Example,

We will find the Employee Records who has “Marking “in their Job Title.

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

  • Separates the string into individual words based on word boundaries (word-breaking).
  • Generates inflectional forms of the words (stemming).
  • Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

USE AdventureWorks2008
GO

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing OR Assistant');

SELECT BusinessEntityID,JobTitle
FROM HumanResources.Employee
WHERE CONTAINS(JobTitle, 'Marketing AND Assistant');
GO

Conclusion

Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

129 thoughts on “SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

  1. We use SQL 2008 Full-Text here

    It works good, most of the time

    However it is really a black box that’s hard to customize, especially if you want to weight different columns, different terms, etc…

  2. Hi,

    I installed recently SQL2008 on a new server. I moved my databases from the old server(SQL 2005) to the new one.
    Everything is woeking fine…except Full text search.

    FTS is very slow on SQL 2008, I deleted and recreated the FTS from scratch but still very slow.

    Can somebody has a similar issue?

    Thx.

    • Whenever you run FTS for the first time. It will take very long time to load your data. But once it’s completed means you get the required results. Then you will not have any performance issues.

  3. Great Post!

    I have started creating a full text index and I have a question –

    If I create a Full Text Search Index on multipal tables should I be creating a new catelog for each tables?

  4. Thank you for the walk though. Very informative as usual. A very minor detail but one that might confuse some readers is that you wrote:

    “We will find the Employee Records
    who has “Marking“ in their Job Title.”

    I believe the word “Marking” should have been “Marketing”.

  5. hi,
    This post is very nice. I developed fulltext indexing on one table in sql server 2005. Initially it worked well but now its dead slow ,please any one suggest me what to do, client is not satisfied with this one.

    Thanks

  6. I tried this example with the Adventurer dbase and I get the following error.

    Msg 30046, Level 16, State 1, Line 2
    SQL Server encountered error 0×80070218 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

    I have checked that service (SQL Full-text Filter Daemon Launcher (MSSQLSERVER)) is running and I have run the sp (sp_fulltext_service ‘restart_all_fdhosts’) and that also gives me the same error.

    Any Ideas?

    THnx John

  7. @John, I had the same problem. Try going to Sql Server Configuration Manager and change the full-text daemon launcher to use the same account as the SQL Server instance. That fixed it for me.

    Start -> Programs -> Microsoft Sql Server 2008 -> Configuration Tools -> Sql Server Configuration Manager

  8. hi.. Sir

    how can i use column name in freetext string field

    exp :

    select cat_name from tbcat_res c,tbanswer a where freetext(c.cat_key,a.ans_cat)

  9. Hi,

    I installed and migrted my data from sql2000 to sql2008.

    Everything was migrated successfully except that I’m having an error when trying to fulltext index a image field.

    Can somebody help me on this one..

    thanks..
    mon

  10. Hi all,

    I’m getting below error when I run query (I have created full text catalog)

    Msg 30046, Level 16, State 1, Line 1
    SQL Server encountered error 0×80070422 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

    What would be the reason for this ? Please help me to resolve this problem.

    Thanks.

  11. Hi Pinaldev,

    i’ve a full text search catalog on my DB(ex: abcd) and while populating it ran successfully but the population status was still idle.

    The abcd DB is restored from another SQL server machine. both sql server configurations are same as mentioned below.

    while checking the SQL server log i’ve found the below error message on the FT catalog population process.

    “SQL Server failed to communicate with filter daemon launch service (Windows error: The service cannot be started either because it is disabled or because it has no enabled devices associated with it.). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.
    Error: 9954 Severity: 16 State: 1.”

    i’m using SQL Server 2008 (Standard Edition, version: 10.0.1600.22)

    To run the Full-Text search catalog successfully, what i’ve to do. please give me any suggesstions to run this FT search.

    Thanks, Rosebabu.

  12. Hi,

    The error stated on my comment is fixed by starting “SQL Full-text Filter Daemon Launcher” service. and i made this service to start automatically.
    Now i’m not having the error as ‘SQL Server failed to communicate with filter daemon launch service…”.

    while trying to populate the FT catalog now, i’d error on the “Full-Text Search crawl log”. the log file was located in %ProgramFiles%\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG folder

    and in that folder the log file has the following error message.

    “A fatal error occurred during a full-text population and caused the population to be cancelled.
    Population type is: FULL;
    database name is adj001 (id: 9);
    catalog name is adjcat001 (id: 11);
    table name tblAssociation (id: 2117582582).
    Fix the errors that are logged in the full-text crawl log. Then, resume the population.
    The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION.”

    Please give any suggesstion to run the FT searching successfully on the db server.

    Thanks,
    Rose.

  13. Hi,

    Never Mind for further discussion,

    Finally i got the solution for my problem (Full Text Index Searching).

    while installing the SQL server 2008 i selected all the services to be installed and the service a/c as Network Service.

    Now i went to Administrative Tools->Services then i selected the “SQL Full-text Filter Daemon Launcher” service.
    then right click it and on the “Log on” tab, i changed the log on as “Local System Account” and restarted the service.
    and as well as i did for the “SQL Server Agent” and restarted the service.

    Now the Full text searching is populating and running fine.

    Thnks.

  14. Hi,

    This article has been great, but one question.

    How do you order by the relevance from the results given?

    i.e. SELECT BusinessEntityID,JobTitle
    FROM HumanResources.Employee
    WHERE CONTAINS(JobTitle, ‘Marketing OR Assistant’);

    You should expect to ‘Marketing Assistant’ to be at the top of the results… . I see in your example you have scrolled down to row 4 so it looks like it is ordered by relevance.

    Or my own example it certainly hasn’t ordered by relevance so is there any way of doing this?

    Many Thanks

    Shane

  15. Hi,

    I have a big problem. Full-Text Index option is greyed. I have installed SQL Server 2008 on Windows Server 2003 R2. While installing I have selected all components to get installed.

    Now I need to create an Index on a table as it is given by Pinal. But found that Full-text Index option is greyed(disable). I searched on many websites and blogs but didn’t find any suitable solution.

    Looking for your support..

    Thanks in advance

  16. Hello,

    I want to search a database column for a phrase, but the words can be in any order or even less. I figured FREETEXT function, looks at the meaning and not at the exact phrase. how can I order my query results by their weight/rank

    for example: search phrase is ” The FOX Rocks 99.3 FM ”

    my Data base column can contain:

    the fox
    the fox rocks
    the fox rocks 99.3 FM
    The fox Rocks 99.3
    Fox Rocks 99.3 FM
    the fox rocks fm
    fox is an animal <——————I dont want to include this record since 1 word matches

    As we can see the third record has the closest match, can I get the results:

    the fox rocks 99.3 fm
    the fox rocks 99.3
    Fox Rocks 99.3 FM
    the fox rocks fm
    the fox rock
    the fox

    Thank you,

  17. I followed everything above but is having trouble indexing pdfs. I installed the ifilter so pdf is one of my document types, however, I am unable to search through pdfs. I can search through docs fine but not pdfs. Have any idea what my problem could be?

  18. Hi Pinal,
    I am getting the following error while try to create fulltext search.I hope u wil help about it..

    TITLE: Microsoft SQL Server
    ——————————

    This wizard will close because it encountered the following error:

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1406.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    Could not load type ‘Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection’ from assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′. (SqlManagerUI)

    ——————————
    BUTTONS:

    OK
    ——————————

  19. Can you show an example using varbinary(MAX) and store some document such as a Word doc?

    I tried it and I dont get any search results. When I remove the varbinary(max) column and just do a search on the title field for example, I get results…

    I’m wondering if you’ve successfully stored a document in a varbinary(MAX) column and used full-text search to index it and search against it using CONTAINS or FREETEXT…

    It isn’t working for me…

  20. Thank U Very Much.

    But I Have some doubts.

    Please Explain with details the following Queries cost and execution Plan

    SELECT pageid,pagename
    FROM dbo.Pages
    WHERE FREETEXT(*, ‘Recruitment Targets’);

    SELECT pageid,pagename
    FROM dbo.Pages
    WHERE CONTAINS(PageName, ‘Recruitment’);

    select pageid,pagename
    from dbo.pages
    where Pagename like ‘Recruitment%’

  21. Thank you very much. Good step by step instruction.
    I have SQL Server 2008 Express with Advanced Services.
    I was unable to enable the Full-Text Filter Daemon Launcher from the Configuration Manager.
    Using the stored procedures in this article worked well as an alternative way to enable full text on a database.

  22. i am getting following error when i tries to use full text catalog for more than 10 times.

    There is insufficient system memory in resource pool ‘default’ to run this query

    please help me to resolve same

  23. Query Problem
    ——————–

    Select * from mst_Table
    where contains(resume,’”Cognos 8″‘)

    I require that the SQL query should output “Cognos 8″ and not “Cognos” or “8″.

    currently when i run the query i get results such as

    Environment: Cognos Impromptu 6.0, Cognos Power play 6.6, IWR Server 6.0, Cognos Scheduler, PowerPlay Enterprise Server, IIS Web Server. Open Solutions, Austin TX Feb 03 – April 04 QA analyst.

    In this above paragraph “Cognos 8″ is not present still the query outputs this row.

    please help.

  24. Dear Pinal,

    Please give some time from your busy schedule.
    I have an issue in Full Text Search. I have few resume and i have posted that resume in one table. i have configured with full text search. In that table i have data of all resume i have. suppose two resume have content “Jayant Solanki”. Whenever i tried to search word “Jayant”. it will display proper result. i have another data like “Ajay Jain.”. I tried to search like “jay”. Now i want result data of “Jayant” and “Ajay” because “jay” is available in both the name. So how could i do that? Please help. Any suggestion?

  25. Hi,

    I would like to know whether the part of word can be used in CONTAINS to get the match.

    For e.g. we can match the part of the word in LIKE with the help of %

    Select * from table_name where column_name like ‘test%’

    which will return the result even if its having a row with ‘testing’ since it starts with ‘test’

    How can we get the same result in CONTAINS. It always returns the exact word match. Please help

  26. Hi Pinal,

    I have created FTI in SQL Server 2005 and i am trying to use HTML iFilter with FTS. But always i am getting zero row as a result. can u please help me. Please review following detail.

    Table Structure :
    ———————
    Id – int (PrimaryKey)
    Name – nvarchar(50)
    SearchData – varbinary(MAX) (full text index column)
    DocType – nvarchar(10) (full text index type column)
    ———————

    I have to store data in HTML content of different language (i.e. English, Spanish, etc.)

  27. Hi Pinal Dave,

    I would like to give Arabic text search word break in catalog.
    there is not option to choose the Arabic.
    is there any thing need to install ?

  28. How does one use FTS to search for an exact phrase? I thought placing double-quotes around the phrase inside of the single quotes with CONTAINS would for it to search for a single phrase but that does not work. For example…

    CONTAINS(col1, ‘ “this is a complete phrase” ‘)

    will still return rows that contains some of the words but not the complete phrase. So how does one search for an exact phrase with FTS?

    • Hi Don, Did you ever get a solution to your question? I have the similar issue. Please let me know how did you solve the single phrase searching.
      Also I have brackets in the search phrase. Thank you!

      • Hi Janki – Sorry no. Apparently this is another Microsoft product that sort of works but has enough gotchas that using it a challenge. If your search phrase works without the brackets, you may have to write code to remove them before the phrase is passed into the contains clause. I have learned that some symbols and also any words or characters in the exclusion list can affect the search. If you find a solution, please share back here.

        • Don, Thanks for responding! When I search for a phrase eg. ‘highly advantageous’ it works fine, but when I search only for the word ‘highly’ it returns me documents where ‘highly’ is not present at all..not sure what can be the issue here.
          Regarding the search phrase having brackets, its not working…If I remove the brackets and search, then it doesnt return any result because 22 (X)(1)(F) then becomes 22X1F. I will keep on trying and will let you know if it works ever!

  29. 2 issues:
    1 – FTI slowing down search results. When we have FTI enabled, the search results are slow. Anyone know why this is or how performance can be improved?
    2 – looking for way the search can find segments of a word. IE: return an item ABC123 when I search for BC. Anyone know how to do this?

  30. Hi there,

    Good Article, But I’m having problem with Varbinary(Max) data Type, so that i can use for doc/xls/pdf file.

    Urgent.

    Please help me

    Abu

  31. Hi Pinal,
    I am using MSSQL Server 2005 with the Full Text Search on the Column
    tblProducts.ProductName and tblProducts.KeywordsUser

    When I run the following query I could not get any result for the specific word phrase Mirror My Soul.
    Can you help to find the issue with the word.
    This query is working for another word e.g. One Small Drop
    SELECT tblProducts.*
    FROM tblProducts
    INNER JOIN CONTAINSTABLE (tblProducts, (ProductName,KeywordsUser),’”*Mirror My Soul*”‘) AS KEY_TBL
    ON tblProducts.ID = KEY_TBL.[KEY]

    Any help to find out the issue is appreciated.
    Regards
    Shyam

  32. Hi. I have SQL 2008 r2 installed on my pc. I want to upgrade with advance services or full text search only, through sql 2008 r2 advance(795 mb setup). Is it possible.? I don’t want to install whole sql 2008 r2.

    Regards
    Ankit

  33. Hi
    I am using MSSQL Server 2005 with the Full Text Search.
    Query with Contains and like not give the same results
    My Query is

    Select * from Searchtable where (ipccode like ‘%A61K 36/00%’ or ipccode like ‘%A61P 17/00%’) and (ipccode like ‘%C13K 13/00%’ and ipccode like ‘%A61P 1/00%’)
    Its gives the 78 results and

    Select * from Searchtable Where ( Contains( IPCCode ,’(“A61K 36/00″ or “A61P 17/00″) and (“C13K 13/00″ and “A61P 1/00″)’) )
    Its gives the 355 results

    both are same query first with Like and second with Contains, why results come with differents.

    Any help to find out the problem
    Thanks and Regards
    Manohar

  34. Hi,
    we have bilingual site english & arabic.
    in English free text search, its working perfectly for any word and all word search.
    i need to search for Arabic any word & all word.
    can any one give me step by step procedure or any links ?
    i tried with Arabic it searches only with all words.

    in the table column there is option to set word break;
    i can choose only english;

  35. Hi Friends,

    I am working on SQL Server 2005, I would like grouping the full text index. Take an example: I have three tables 1) Employee 2) Supplier and 3) Client. All this tables have id and name fields. I would like to create the index such a way that if I will search with “John OR Thomas”, it will search on all these tables together and return the records based rank descending order.

    Can we achieve this?

    Thanks in advance
    Krunal Panchal

  36. Dear sir,
    this is nice article, i have found one tricky problem with Full text indexing.
    Os. window server 2003 32 bit
    sql server: sql server 2008 Enterprise edition

    i have applied full text index with track change: automatic. and normal cpu uses is 1-4%
    but some time a cpu uses go to 100% all memory is taken by sqlserver.exe, population status : idle
    last population date: 5 minute before current date.
    this cpu uses remains 100% until next track changes does not occurred ( population date change after 10-20 minutes.)

    please help me.

    Thanks in advance.

    –nilesh

  37. The image screenshots are not loading for part of the Tutorial. I’ve gone as far as “Populate the Index” which I guess is important because searching the table returns no results.

    Thanks for the wonderful resources you offer…

  38. Hi ,

    I had an issue with the full-text index in the sql server .
    What i need is if i can search with the singular word it has to get the results for plural word to respective singular word.
    I have tried this by enabling the full-text index in the specific table.But i am not able to get those results exactly what we except .

    Please anyone help can be really appreciated in Advance.

    Thanks .

  39. USE AdventureWorks
    GO

    SELECT ManagerID, Title
    FROM HumanResources.Employee
    WHERE FREETEXT(*, ‘Marketing Assistant’);

    SELECT ManagerID,Title
    FROM HumanResources.Employee
    WHERE CONTAINS(Title, ‘Marketing OR Assistant’);

    SELECT ManagerID,Title
    FROM HumanResources.Employee
    WHERE CONTAINS(Title, ‘Marketing AND Assistant’);
    GO

  40. Can you please clear out my doubts:
    There is one table say tbl_Test .I have created fulltext index on this table for search purpose.But if when I am going to add some record in this table from the site then my site speed goes down. Is this related to indexing applied on this table.

  41. I have problems with whitespace :

    SELECT count(ID) FROM A
    WHERE Contains(A.titlefile,’a’)! This query works well!

    SELECT count(ID) FROM A
    WHERE Contains(A.titlefile,’a c’)!//This query throw exception : systax is error at ‘a’.

    Help me please

  42. I have started the SQL Full-text Filter Daemon Launcher but am still unable to see the “Storage” node under the database (in this case AdventureWorks2008). I had attached a .mdf file for full text searching but the node is not there!! please help!!

  43. hi,
    i have 2 records in my database.
    in the first record, i have the word ‘spoiled’ in it.
    in the second record i have the word ‘spoiler’ in it.

    my query is:
    select*
    from products
    where freetext(*, ‘spoil’)

    the problem is that as results i am only getting 1 record which is the first record. am i doing something wrong?

    thanks for any help in advance

  44. I have tried and tried and no matter what I do I cannot get this to produce results when I search the table.

    Here are the steps I used to create …

    CREATE TABLE HR_Documents(DOCID int identity not null,
    DOCUMENT VARBINARY(MAX) NULL,
    DOCTYPE VARCHAR(10) NULL,
    CONSTRAINT PK_HR_Documents
    PRIMARY KEY (DOCID))

    CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT

    CREATE FULLTEXT INDEX ON dbo.HR_Documents(DOCUMENT TYPE COLUMN DOCTYPE)
    KEY INDEX PK_HR_Documents ON ftCatalog
    WITH CHANGE_TRACKING OFF

    – Declare a variable to store the image data
    DECLARE @Doc AS VARBINARY(MAX)
    – Load the image data
    SELECT @doc = CAST(bulkcolumn AS VARBINARY(MAX))
    FROM OPENROWSET( BULK ‘Test Resume.docx’, SINGLE_BLOB ) AS x
    – Insert the document
    INSERT INTO HR_Documents (DOCID, DOCUMENT, DOCTYPE)
    VALUES (1, @doc, ‘.doc’ )

    Then when I perform a query, I get no results.

    SELECT DOCID, DOCUMENT, DOCTYPE
    FROM HR_Documents
    WHERE CONTAINS(DOCUMENT, ‘%great%’)

    Can you spot my problem?

    • I will try this out soon. Looking at the text everything looks fine and I do not see problem. I am not sure at this point. Let me send it to few friends may be they can spot it immediately. I will try this out on example and see if I can get back to you soon.

  45. PS, this is just a sample resume that I loaded, the text looks like this. As you can see the word “great” is in there …

    Resume for: Joe Bloggs
    I’m a great guy with all the skills you’re after:

    • Aaaaaaaaa
    • Bbbbbbbbbbbbbb
    • Cccccc
    • Ddddddddddddddddddddddddddddddddd
    • Eeeeeeeeeeeeeeeeeeee

    Thanks for the chat.

  46. Hi Pinal Dave,

    Full text search is not available on SQL Azure. Is there an alternative search technique that we can use on SQL Azure.

  47. Hi Pinal,

    I have created a full text index to search pdf content using a keyword. The problem is that if after adding a pdf and searching it immediately, it does not give the result using full text search, but I can see the row added in the table. If i delete the full text index and create and refresh the database, then the pdf appears on searching.

    I followed the same steps provided above to create full text in sql server 2005

    Thanks

  48. Hi

    If word is too large (more than 65 characters) then FullText search is not working in SQL Server 2008.

    Table Employee is having value “MostViewModelclassesneedthesamefeaturesTheyoftenneedtoimplementtheINotifyPropertyChanged” for column FullTextSearchContent in one of the row.

    Following query works properly.
    SELECT * FROM
    Employee
    WHERE Contains(FullTextSearchContent,
    ‘ “MostViewModelclassesneedthesamefeaturesTheyoftenneedtoimplementtheINotifyPropertyChanged” ‘)

    Following query will not return any row as ‘*’ is being used. Here if i keep my word to 64 characters then this will also work.
    SELECT * FROM
    Employee
    WHERE Contains(FullTextSearchContent,
    ‘ “MostViewModelclassesneedthesamefeaturesTheyoftenneedtoimplementtheINotifyPropertyChanged*” ‘)

    Please let me know why this happens.

  49. Table: Customer
    Full Text Index on: strLOB, strMGL, strName

    —————————————————————————————————–
    strLOB strMGL strName
    —————————————————————————————————–
    COMPLETE PURCHASING Fred Babbie Fred Victor Centre
    COMPLETE PURCHASING Fred Babbie Cardinal Flahiff Basilian Centre
    COMPLETE PURCHASING VACANT Basilian Center
    COMPLETE PURCHASING Fred Babbie Fred Douglas Lodge
    COMPLETE PURCHASING Fred Babbie Fred Douglas Heritage House
    ——————————————————————————————————

    Need to fetch rows having words ‘Fred’ AND ‘Basilian’ both. I tried the below query but no result found.

    SELECT strLOB, strMGL, strName
    FROM Customer
    where contains(*, ‘Fred AND Basilian’)

    Please let me know how this can be achieve.

  50. Table: Customer
    Full Text Index on: strLOB, strMGL, strName

    ———————————————————————————————————-
    strLOB strMGL strName
    ———————————————————————————————————-
    COMPLETE PURCHASING; Fred Babbie; Fred Victor Centre
    COMPLETE PURCHASING; Fred Babbie; Cardinal Flahiff Basilian Centre
    COMPLETE PURCHASING; VACANT; Basilian Center
    COMPLETE PURCHASING; Fred Babbie; Fred Douglas Lodge
    COMPLETE PURCHASING; Fred Babbie; Fred Douglas Heritage House
    ———————————————————————————————————

    Need to fetch rows having words ‘Fred’ AND ‘Basilian’ both. I tried the below query but no result found.

    SELECT strLOB, strMGL, strName
    FROM Customer
    where contains(*, ‘Fred AND Basilian’)

    Please let me know how this can be achieve.

  51. Hi,

    full text search working for varchar columns but i need full text search for varbinary(max) column and column not accepting while creating full text index columns

  52. A Note on Full Text, Performance, and Versions:
    As always, it is a joy to read Pinal’s posts. Thank you! I would like to add my research in this area. I have been looking into solving the search query challenge in my database and have found some helpful additions to the data above.

    (1) Why does Full Text Indexing offer better performance than using LIKE keywords or other non-full text Indexed approaches?
    Answer: LIKE ‘%searchWord%’ will need to perform a scan of all items in a given column; indexing is not used. If you want to optimize on performance using indexes for search patterns you need to enable Full Text Indexing as shown above.

    (2) Which “keywords” are better?
    Answer: This depends on what you need to do. As Pinal stated, CONTAINS has less overhead than FREETEXT. In most cases FREETEXT is overkill. Further, there are a lot of features to CONTAINS to consider. See the MSDN site for more details, or URL below. Note: FREETEXT and CONTAINS do not offer “priority” features directly. You can use the FREETEXTTABLE or CONTAINSTABLE predicates for this capability (aka. the other two full text predicates avaialble)

    I found this “sitepoint” article very insightfull: http://www.sitepoint.com/sql-server-full-text-search-protips-part-2-contains-vs-freetext/

    (3) Differences in Deployment of Full Text Indexing:
    – SQL 2005 and earlier you need to depend on the external service (MS Search Services … I think they were called)
    – MSSQL 2008 + finally put this feature into the product (no external service, and backups include indexed files / catalogues)
    – MSSQL 2012 has made some changes to word breaking and other features. You will need to rebuild indexes if you are moving to this version.

    Hope this is found usefull by all you fine coders out there! Happy indexing, and special thanks to Pinal for his amazing educational guides!

  53. How are full text catalogs and indexes implemented in 2008 and higher? I see some MS articles that claim a folder is created for the catalog and other places says the implementation is virtual but having problem finding out what that means. Thank you for you blog and your great site.

  54. When I’m searching for ean references like 5413736209503 in a full text index catalog I’m getting far more results than exptected (5000+) instead of only one specific article. What’s blocking the search for numbers only references?

    Any ideas?!

  55. Dear Pinal,

    I have created full-text search on the table and consider two columns only.
    Example
    Table Name : Tank

    Tank Name | Fuel Name
    [1] V01 | JP8
    [2] V02 | DSLW
    [3] BAG01 | JETA1
    [4] BAG04 | AVGAS

    if i use below state and not return the rows.
    select tankname,fuelname from tank where contains(*,’”v01″ and “jp8″‘)

    AND operator check both value in two column and not return the row.
    OR condition is working.

    Please let me know how this can be achieve.

    Thanks,
    Utpal

  56. Greetings Team,

    Please anyone help me on this ….

    I have some data ,

    In procedure going to Retrieve data using like operator its working fine but my requirement is exact match will come first followed by other liking data…
    please let me know any way to display records ..asap plz

  57. I have very large data ,
    i want search both title and description field ,indexing already implimented on table but while we search on abstract field then it takes more time to retrive data pls help me how can i relevence search.

  58. Hi Pinal,

    I have a SQL 2008 database that was moved off a corrupted system. Problem is they didn’t take the Full-Text catalog. Now I can’t seem to find any way to reattach the database and it’s log file to a new SQL 2008 instance. It’s throwing errors about the catalog missing. Is there a way to fake it out, or to simply attach using a script that informs SQL to NOT attempt to utilze the FTCAT that the database is referencing?

    -Rob

  59. I need something like this

    SELECT BusinessEntityID,JobTitle
    FROM HumanResources.Employee
    WHERE CONTAINS(JobTitle, ‘Mark’);

    And return all rows that contains Mark, such as
    markert
    marketing
    mark
    but does not work.

    What can i do?

  60. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  61. Hi All,
    Say that my table gets refreshed every week with millions of records, in this case shouldn’t Full Text Index be refreshed every week as well or it takes care of it automatically?

    - Siva Prasad

  62. I am currently using IN clause on a varchar field. Will using Contains of FTS help in performance? For e.g.

    Select * from Orders where City IN (‘London’ , ‘New York’)
    v/s
    Select * from Orders where Contains (City, ‘London or New York’)

  63. Hi,
    I am using freetexttable for bringing search results but when i am using ‘a+’ word, it is not bringing the results with exact word ‘a+’, it is bringing results with ‘a’ only. please help me.

  64. Hi Pinal, Thanks for the article
    Is this feature similar to tsvector and tsquery datataypes in postgresql ?
    I am doing a project where i need search on ICD10CM database. Can this feature be useful in doing that ?

  65. Hi Pinal,
    I have been writing a store procedure for a searching few joined tables,(eg customer table,product table,category table) I have used like ‘% @searched word % for major column including all 3 tables.
    Q1: Which one would have good performance like or Full Textsearch ?
    Q2:Can I have full text search query by joining 5 to 6 table linked with each other through pk,fk

    Thanks,
    Ashraf

  66. Hi Pinal,

    Can you please share your Idea For Full Text Search v/s Solr Search. which is better for very large database( around 75 GB+)
    Today Also I had Try to search with Solr API(http://lucene.apache.org/solr/). it Give Result of 28K Searchable Result in Just 4 ms(MYSQL Database).So this criteria is also work with SQL Full Text Search.and i Get same result in same or less then this duration.
    Because I want to transfer my Database mySQL to SQL Server. and my Application from PHP To .Net.

    So please tell me better to use ? Solr or SQL Full Text Catalog.

    Thanks,
    Chirag

  67. Hi,

    how to increase the query performance of full text search if we created full text search into 3 tables.

    Select *
    FROM table1 T1 Inner join Table2 T2
    ON T1.Id = T2.Id Inner join table3 T3
    ON T1.Id = T3.Id
    Where FreeText(T1.ProductName,@FulltextSearch)
    OR FreeText(T2.Keywords,@FulltextSearch)
    OR FreeText(T3.Summary,@FulltextSearch)

    Please help on it….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s