Problems creating a full text index on a view

问题:

I have a view which has been created like this:

CREATE VIEW [dbo].[vwData] WITH SCHEMABINDING
AS
    SELECT  [DataField1] ,
            [DataField2] ,
            [DataField3]
    FROM    dbo.tblData

When I try to create a full text index on it, like this:

CREATE FULLTEXT INDEX ON [dbo].[vwData](
[DataField] LANGUAGE [English])
KEY INDEX [idx_DataField]ON ([ft_cat_Server], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

I get this error:

View 'dbo.vwData' is not an indexed view. 
Full-text index is not allowed to be created on it.

Any idea why?

回答1:

you have to make your view indexed by creating unique clustered index:

create unique clustered index ix_vwData on vwData(<unique columns>)

After that, index idx_DataField must be a unique, non-nullable, single-column index.

回答2:

First you need to create a unique clustered index on a view, before creating a fulltext index.

Suppose you have a table:

CREATE TABLE [dbo].[tblData](
    [DataField1] [Varchar] NOT NULL,
    [DataField2] [varchar](10) NULL,
    [DataField3] [varchar](10) NULL
    )

And as you already did, you have a view:

CREATE VIEW [dbo].[vwData] 
WITH SCHEMABINDING
AS
    SELECT  [DataField1] ,
            [DataField2] ,
            [DataField3]
    FROM    dbo.tblData
GO

Now you need to create unique clustered index on a view :

CREATE UNIQUE CLUSTERED INDEX idx_DataField
    ON [dbo].[vwData] (DataField1);
GO

After the unique key is created since you already have fulltext catalog ft_cat_Server you can create a fulltext index:

CREATE FULLTEXT INDEX ON [dbo].[vwData](
[DataField1] LANGUAGE [English])
KEY INDEX [idx_DataField]ON ([ft_cat_Server], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

Hope this helps :)