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

Question

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?

score 46 · Accepted Answer · answered Feb 21 '10 at 18:35

46

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".

answered Feb 21 '10 at 18:35

1

Thanks i was trying to use column name :d – Furkan Gözükara Feb 21 '12 at 21:36
http://technet.microsoft.com/en-us/library/ms187317.aspx "KEY INDEX index_name - Is the name of the **unique key index** on table_name." – quentin-starin Sep 20 '13 at 18:38
Thank you for pointing me in the right direction! IN **SSMS** if you open the `+` on your table and go to `Keys` the gold highlight name that starts with `PK_` is your Primary Key. Again, thank you! – Termato Dec 10 '14 at 19:29
After typing `exec sp_help DomainName` its gives error `Msg 15009, Level 16, State 1, Procedure sp_help, Line 100 The object 'DomainName' does not exist in database 'Test' or is invalid for this operation.` PLEASE HELP – RU Ahmed Jul 06 '16 at 01:01
1

resurrecting this after a while, but, when they said 'DomainName', they meant the table name – DataGeek Jan 29 '20 at 20:26

score 4 · Answer 2 · answered Feb 21 '10 at 18:37

Instead of specifying the column name, specify the name of the index. You can find the name of the primary key index like:

select name from sysindexes where object_id('DomainName') = id

Then you can create a fulltext index like:

CREATE FULLTEXT INDEX ON DomainName
(
    MiddlePart 
    Language 0X0
)
KEY INDEX PK__DomainName__40E497F3 ON domaincatalog
WITH CHANGE_TRACKING AUTO

zloctb · Answer 3 · 2015-07-13T16:14:15.833

https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

CREATE TABLE ProductDocs (
  DocID INT NOT NULL IDENTITY,
  DocTitle NVARCHAR(50) NOT NULL,
  DocFilename NVARCHAR(400) NOT NULL,
  FileExtension NVARCHAR(8) NOT NULL,

      DocSummary NVARCHAR(MAX) NULL,
      DocContent VARBINARY(MAX) NULL,
      CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)
    )
CREATE FULLTEXT INDEX ON ProductDocs
(DocSummary, DocContent TYPE COLUMN FileExtension LANGUAGE 1033)
KEY INDEX PK_ProductDocs_DocID
ON ProductFTS
WITH STOPLIST = SYSTEM

The first line of the statement includes the ON clause, which specifies the table name (in this case, ProductDocs). The statement’s next line is a list of the columns that should be indexed (DocSummary and DocContent).

The next line of the CREATE FULLTEXT INDEX statement in the preceding example is the KEY INDEX clause. This is the name of the unique key index (in this case, PK_ProductDocs_DocID) that is defined on the ProductDocs table. Be sure to specify the index name, and not the column name, when defining your full-text index.

Following the KEY INDEX clause in the full-text index definition is the ON clause, which specifies the name of the full-text catalog (ProductFTS) that the index will join. In SQL Server 2008, you can also specify a filegroup where the index will be stored. However, this option isn’t available in SQL Server 2005 because filegroup association is at the catalog level.

The final clause in the example CREATE FULLTEXT INDEX statement is WITH STOPLIST. This option, available only in SQL Server 2008, lets you specify the name of the stoplist that will be used for this index. In this case, the system stoplist is used, but you can instead specify a user-defined stoplist. (Stoplists are covered in more detail later in the article.)

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

3 Answers3

Linked