CODEWITHSUNDEEP
sql-serverfull-text-search
Nathan Ridley
Nathan Ridley

Reputation: 34396

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

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?

Upvotes: 31

Views: 23063

Answers (3)

zloctb
zloctb

Reputation: 11177

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

Upvotes: 2

Andomar
Andomar

Reputation: 238058

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

Upvotes: 4

user121301
user121301

Reputation:

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

Upvotes: 49

Related Questions