Creating a fulltext index on a view in SQL Server 2005

Question

I am having troubles creating a fulltext index on a view in SQL Server 2005. Reviewing the documentation I have not found the problem. The error message I receive is: "'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, and has maximum size of 900 bytes. Choose another index for the full-text key." I have been able to verify every requirement in the errorstring except the "offline" requirement, where I don't really know what that means. I'm pretty darn sure its not offline though.

I have the script to create the target table, view, and index below. I do not really need a view in the sample below, it is simplified as I try to isolate the issue. If you live in the Seattle area and get this one I will buy you a coffee, thanks.

DROP VIEW [dbo].[ProductSearchView]
DROP TABLE [dbo].[Product2]
GO


SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE TABLE [dbo].[Product2](
    [Id] [bigint] NOT NULL,
    [Description] [nvarchar](max) NULL,
    CONSTRAINT [PK_Product2] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[ProductSearchView] WITH SCHEMABINDING
AS
SELECT   P.Id AS Id,  
         P.Description AS Field
FROM [dbo].Product2 AS P
GO

-- this index may be overkill given the PK is set...
CREATE UNIQUE CLUSTERED INDEX PK_ProductSearchView ON [dbo].[ProductSearchView](Id)
GO

-- This is the command that fails
CREATE FULLTEXT INDEX ON [dbo].[ProductSearchView](Id, Field)
KEY INDEX Id
ON FullText WITH CHANGE_TRACKING AUTO;
GO

Answer 1

Hi Frank,

You need to specify the name of the index instead of the column name when creating the fulltext index:

CREATE FULLTEXT INDEX ON [dbo].[ProductSearchView](Id, Field)
KEY INDEX PK_ProductSearchView
ON FullText WITH CHANGE_TRACKING AUTO;
GO

This will remedy the error you are getting, but it will give you another error because you are trying to include a non-character based column in your text search. You may want to choose another indexed character column to use in your full text catalog instead.

Hope that helps!

Answer 2

Thanks, that did the trick. I did not intend to include Id as an column in the fulltext index, so I will just remove it as well to avoid the second problem.