Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 248
Tola Odejayi Avatar asked Sep 22 '13 15:09

Tola Odejayi


People also ask

What is the best way to create full-text indexes?

To create a full text index choose your table and right click on that table and select “Define Full-Text Index” option. Now select Unique Index. It is compulsory that for “Full Text Index” table must have at least one unique index. Select columns name and language types for columns.

Can an index be created on a view?

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.

How do I know if full-text indexing is enabled?

How can I tell if Full-Text Search is enabled on my SQL Server instance? A: You can determine if Full-Text Search is installed by querying the FULLTEXTSERVICEPROPERTY like you can see in the following query. If the query returns 1 then Full-Text Search is enabled.

How do I create a full text search index?

Create FULLTEXT index using CREATE TABLE statement. To create the FULLTEXT index, you place a list of comma-separated column names in parentheses after the FULLTEXT keyword. The following statement creates a new table named posts that has a FULLTEXT index that includes the post_content column.


1 Answers

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.

like image 140
Roman Pekar Avatar answered Nov 08 '22 16:11

Roman Pekar