SQL Server 2005 Express Full-Text Search Setup Tutorial

I recently had to setup a search facility on a website driven by SQL Server 2005 Express. My natural choice was SQL Server’s Full-Text Search feature, but setup was not entirely intuitive, and the web was full of misinformation. I’ve put together a tutorial to save you time.

If you’re not sure if your have Full-Text installed, read my post on installing SQL Server 2005 Express Full-Text Search.

Once, Full-Text is installed, you need to create your index:

Managing your index is then actually very simple:

• Browse to the tables of your database, right click and select ‘Design’
• In the main window, right click and select ‘Fulltext Index…’
  If you see the message, ‘You need to define one or more full-text compatible indexes on the table to create a full-text index’ and the ‘Add’ button is greyed out, it’s because you don’t have a column the database can use to uniquely identify each row. Make sure you have a primary key, and if you’ve no success, start your troubleshooting with this video – it discusses unique indexes about 1/3 of the way through.
• Otherwise, click ‘Add’, and you have a new index.
• To configure the columns in the index, click the ‘…’ button next to ‘Columns’
• For each column, select the column, and select the appropriate language
  To understand why the language parameter is important, see my post on use of the FORMSOF and INFLECTIONAL keywords in SQL Full-Text Search

It’s that easy! Any questions, let me know and I’ll do my best to help.

7 Responses to SQL Server 2005 Express Full-Text Search Setup Tutorial

1. Pingback: Troubleshooting your FORMSOF & INFLECTIONAL searches not working, SQL Server 2005 - Web Strategy Blog

2. Pingback: SQL Server 2005 Express with the Full-Text option greyed out - Web Strategy Blog

3. Rico Marcelo says:

   Hi. The information you have here is very helpful. I was able to create full text index now thanks to you. However, when i right-click on the database name and select “Properties”, then click on “Files”, the checkbox “Use Full-text indexing” remains grayed-out despite the fact that some of my tables already has full-text indexes. Also, when I create new databases the same check box is grayed-out. Hope you can help me out on this. Thanks again.
   By the way i’m using SQL Server 2008 Developer Edition with all components including Full Text installed.

   Reply
4. Iain says:

   Hi Rico, did you check out my related post about the Full-Text option being greyed out in SQL Server?

   Let me know how you get on.

   Reply
5. Mark Berry says:

   Thanks for a very helpful post! It gave me a good start on creating a SQL 2005 Express full-text index. I blogged the full procedure here:

   http://www.mcbsys.com/techblog/2010/07/moving-liberum-help-desk-to-sql-2005-express/

   Reply
6. Mark Wallace says:

   This worked GREAT. Thanks a LOT!!!

   Mark

   Reply

7. Pingback: SQL Server 2005 Express with the Full-Text option greyed out | Web Design Glasgow