Why can't I create this sql server full text index?

sql-server full-text-search
Nathan Ridley picture Nathan Ridley · Feb 21, 2010 · Viewed 15.7k times · Source

I have a database table with the primary column defined as:

ID bigint identity primary key

I also have a text column MiddlePart. I'm trying to create a full text index, like so:

CREATE FULLTEXT INDEX ON domaining.dbo.DomainName
(
    MiddlePart 
    Language 0X0
)
KEY INDEX ID ON domaincatalog
WITH CHANGE_TRACKING AUTO

I get this error:

'ID' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

What am I doing wrong?

Answer

user121301 picture user121301 · Feb 21, 2010

After KEY INDEX, you need to specify the name of the index not the column. To find the name of the index on the column ID, type sp_help DomainName and there will be a list of indexes on that table. The pk will be named something like PK_xxxxx. Use that index name instead of "ID".

Related questions

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

I am getting following error in my SQL server 2008 R2 database: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tblArmy' because it is not full-text indexed.

Read More
SQL Server 2012 Install or add Full-text search

I am working in SQL Server 2012, and would like to use the CONTAINS() function only it seems I need to have full-text search enabled for to be able to use it. How do I enable/install this feature to an …

Read More
How do you get leading wildcard full-text searches to work in SQL Server?

Note: I am using SQL's Full-text search capabilities, CONTAINS clauses and all - the * is the wildcard in full-text, % is for LIKE clauses only. I've read in several places now that "leading wildcard" searches (e.g. using "*overflow" to match "…

Read More