Cannot create index on view 'VIEW_NAME'. It does not have a unique clustered index.

OUTER JOINs are not allowed in index views by defintion.

Here is an example with expanation why.

Basically cause rows can disapear in the view, when data is added in the base table. And the necessary SQL Server logic is to expensive performance wise to implement.

ste5an is quite correct... If you look about a third of the way down in the first link, you will see a lot of things that will upset creating an index on a view. Short answer is you cannot create a clustered index on your view.

Workaround...
1) enable full text on the underlying table and link to that in your queeries.
2) Create another view over the table containing the items you want to enable full-text and link to that view....

create view .... with schemabinding
as
   select the unique PK columns and searchable columns
   from your table
go

then 'create unique clustered index ... ' as you were going to, then the full-text catalogue + index.

by the way, there was nothing attached...

Mark Wills,

With reference to the workarounds suggested, I followed the steps.

1. I  had created a  full text on the underlying table DMS_MANAGEMENT_REPOSITORY. This table already has unique column ID
   CREATE UNIQUE INDEX UI_TBL_DMS_REP_ID ON DMS_MANAGEMENT_REPOSITORY(ID)

CREATE FULLTEXT INDEX ON DMS_MANAGEMENT_REPOSITORY
(
      DOCUMENT_CONTENT_TEXT
)
KEY INDEX UI_TBL_DMS_REP ON DMSRepositoryFTCat
WITH CHANGE_TRACKING AUTO
GO

2. CREATED THE VIEW (attached in the scripts) with schema binding which references the table DMS_MANAGEMENT_REPOSITORY

3. What should I do next to search on the view. Currently everything is working when I query the table but I need to use the View (as I need the department name, sub_department_name ,sub_level_name,document_class_name to be retrieved).

SELECT
      ID,
      DOCUMENT_TITLE,
      DOCUMENT_CONTENT_TEXT,
      DOCUMENT_REF_NUMBER,
      WORK_ORDER_REFERENCE_NUMBER,
      EFFECTIVE_DATE,
      VERSION_NUMBER,
      DEPARTMENT_ID,
--DEPARTMENT_NAME,
      DOCUMENT_CLASS_ID,
 
      SUB_DEPARTMENT_ID,
--SUB_DEPARTMENT_NAME,
      SUB_LEVEL_ID,
--SUB_LEVEL_NAME,
      DOCUMENT_CLASS_ID,
--DOCUMENT_CLASS_NAME,
      FILE_NAME  
FROM
      DMS_MANAGEMENT_REPOSITORY
WHERE
      CONTAINS(DOCUMENT_CONTENT_TEXT,'Microbial ')
View-Script.txt

I followed the instruction and I now understand the way you are suggesting.

When I run the query with the Inner Join, no results are being returned. Just to ascertain the data is existing, I ran a separate query to find if the query fetches records for the critiria 'Microbial' and the rows are returned.

SELECT     *
FROM       DMS_MANAGEMENT_REPOSITORY DMRV WHERE CHARINDEX('Microbial',DOCUMENT_CONTENT_TEXT)>0  

Is there a issue with the INNER JOIN?

It took a time to return the results without the FT Search.

There shouldnt be because it is joining on the PK.

But dont do both. Choose one (probably the Table).

Interestingly enough, you can do the same left joins from the table and still do your FT search on the table - but stick it all in a view and it breaks down.

The following query suggested by you should return rows that have the word 'Microbial ' in the DOCUMENT_CONTENT_TEXT column. Isn't it?

SELECT     *
FROM       DMS_MANAGEMENT_REPOSITORY_VIEW DMRV
INNER JOIN DMS_MANAGEMENT_REPOSITORY_FULLTEXT_SEARCH_VIEW FTS on DMRV.id = FTS.id
WHERE      CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'Microbial ')

I would like to know what I am doing wrong as the ID values is the PK for the respective tables and they should bring the results.

Please Advise, If I run the following query, does it help the get the benefit of the FTS View.

SELECT     *
FROM      
      DMS_MANAGEMENT_REPOSITORY_VIEW DMRV
WHERE
      DMRV.ID IN ( SELECT ID FROM DMS_MANAGEMENT_REPOSITORY_FULLTEXT_SEARCH_VIEW FTS WHERE
                  CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'Microbial'))

Actually the following query is returning results in <3 sec.


SELECT     *
FROM      
      DMS_MANAGEMENT_REPOSITORY_VIEW DMRV
WHERE
      DMRV.ID IN ( SELECT ID FROM DMS_MANAGEMENT_REPOSITORY_FULLTEXT_SEARCH_VIEW FTS WHERE
                  CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'Microbial'))

I just ran up a test, and the inner join does bring back results for my testing. Albeit with very little data. I dont think you are doing anything wrong.

I would have thought that 'where dmrv.id in (...fts.id...) would have proven the inner join. But then, the inner join might be at odds with other criteria when you start to add in other 'where' predicates.

How are you going to be using the FTS ? Do the users have some kind of interface ?

While executing the queries, I observed the following.

I was searching for a Microbial  using the where clause CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'Microbial')), it returned over 23400 rowsin 7 sec.

Got excited, change the query to  CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'Cleaning')), it fetched 9900 rowsin 14 sec.

Now the third search string, I tried out was  CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'SOP')), it returned 51,200 rows in 4 min, 12 sec.

For my ASP.net application to executed, the query needs to be returned in <=30 sec.

Surprisingly, I re executed the third query rows CONTAINS(FTS.DOCUMENT_CONTENT_TEXT,'SOP')), it returned 51,200 rows in less than a second.

What is happening, Should I do something to ensure all the searchable keywords are indexed in some kind.

Please advice.

It would be caching big time. For a first time search, there isnt a lot you can do - apart from making sure your environment is set up as best as possible - e.g. make sure  'max full-text crawl range' is in line with the number of CPU's

It is an advanced option so you need to 'Show Advanced Options' then  sp_configure 'max full-text crawl range', 100   The maximum value is 250, but the default is only 4.

If you are running ASP.Net, then why arent tou using the tables directly ?

Bit of reading : https://docs.microsoft.com/en-us/sql/relational-databases/search/improve-the-performance-of-full-text-indexes
https://docs.microsoft.com/en-us/sql/relational-databases/search/populate-full-text-indexes
https://docs.microsoft.com/en-us/sql/relational-databases/search/improve-the-performance-of-full-text-queries

But we are now getting away from your original question.... and moving into a new topic - being performance.

Thank you  for all the advices and suggestions.