Saturday, March 19, 2011

SQL 2008R2 FULL TEXT INDEX SEARCH SETUP

In Microsoft SQL 2008 R2, the process of setting up full text search is done differently then in prior versions of SQL.


In earlier versions of SQL, we would have to right-click on the table we wanted to have full text index search capabilities by going to Design. Once in the table designer, we would choose Full Text Index and we would set the values in the window that would pop up.


In SQL 2008 R2, however, the process of setting up the full text index search is as follows:


The very first step is to create a new Catalog in our data base as follows (Let's not forget to click on the Data Base we want to setup the Full Text Index Search)


CREATE FULLTEXT CATALOG NameOfCatalog


Execute the stored procedure, which should be really quick and in where we should get a message like this:


Command(s) Completed Successfully


Then we go through the following steps:





1- Right Click the table we want to setup full text index search and then we go to Full-Text index and then choose Define Full-Text Index








2- The wizard opens.








3- Click next - The Primary key (index) of the table will be pre-populated








4- Click next - Here we would choose the columns we want to be searched. In my case, I'm choosing Description and Name.








5- Click next - Here we choose to have SQL update the changes done to the Table Automatically.








6- Click next - Here we select the catalog we want to use for the Full Text Index search (This is the one we created at the beginning of this article)







7- Click next - To define the population I believe we need to have SQL Server Developer or Enterprise. (I left this blank)








8- Click next - Then w see the summary.








9 - Click finish and we will get the following confirmation (This should run fairly quick)




6 comments:

  1. Hi
    I wonder if you could help? I have a full text index in SQL 2008 R2, that's working well. I have a column that contains data thus: 772-1, 772-10, 773-3, and so forth. Does the full text index see/read '772-1' as two words?

    ReplyDelete
  2. Kevski,

    As far as I know, you should be able to search for anything you want. Just make sure you have a primary key. In addition, you should get a resultset based on a specific query (search), and if you were searching for a number like 772-1, the function should find it for you. Again, just make sure you have a primary key associated with the rows.

    I hope this helps.

    ReplyDelete
  3. Unfortunately this isn't the reality, search like 772-1, 772-10 will not return the expected results.

    Check this link:
    http://support.microsoft.com/kb/200043

    ReplyDelete
  4. I can't enable full-text search .
    when i right click on my database ===> Properties ===> Files ==>" Use full-text indexing " don't checked .
    Can you help me .

    ReplyDelete
  5. I get this message
    Cannot use full-text search in master, tempdb, or model database

    ReplyDelete

Thank you for your thoughts. Your comment will appear in my blog shortly after review.

Have a great day!