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

I created a fulltext catalog using the following command.

CREATE FULLTEXT CATALOG [DMSRepositoryFTCat] WITH ACCENT_SENSITIVITY = ON
AS DEFAULT

Before I can create a full-text index on the VIEW  , I am trying to create a Unique Index using the following command.
CREATE UNIQUE INDEX UI_DMS_REP_ID ON DMS_MANAGEMENT_REPOSITORY_VIEW(ID)

But I am getting the error "Cannot create index on view 'DMS_MANAGEMENT_REPOSITORY_VIEW'. It does not have a unique clustered index."

How can I create a Unique Cluster Index on the view  DMS_MANAGEMENT_REPOSITORY_VIEW which has outer join within it. or is there something I am doing wrong.

I am attaching the view script aswell.

Please suggest.
Member_2_7967119Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
1
Mark WillsTopic AdvisorCommented:
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...
0
Member_2_7967119Author Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
You dont need the view to return those columns as such, you just need to join to the DMS tables - albeit like the view already does.
SELECT * 
FROM   DMS_MANAGEMENT_REPOSITORY_VIEW DMRV
INNER JOIN DMS_MANAGEMENT_REPOSITORY DMS ON DMRV.ID = DMS.ID
WHERE CONTAINS(DMS.DOCUMENT_CONTENT_TEXT,'Microbial ')

Open in new window

Unfortunately, the DMS_MANAGEMENT_REPOSITORY_VIEW will never be fulltext enabled with the outer joins.

Your other option was to create a view just for FTS...
CREATE VIEW [dbo].[DMS_MANAGEMENT_REPOSITORY_FULLTEXT_SEARCH_VIEW] WITH SCHEMABINDING
AS
 
SELECT ID, DOCUMENT_CONTENT_TEXT
FROM dbo.DMS_MANAGEMENT_REPOSITORY

GO

CREATE UNIQUE CLUSTERED INDEX IDX_TBL_DMS_REP_FTS_ID ON DMS_MANAGEMENT_REPOSITORY_FULLTEXT_SEARCH_VIEW(ID)
GO

CREATE FULLTEXT INDEX ON DMS_MANAGEMENT_REPOSITORY_FULLTEXT_SEARCH_VIEW
(
      DOCUMENT_CONTENT_TEXT 
)
KEY INDEX IDX_TBL_DMS_REP_FTS_ID ON DMSRepositoryFTCat
WITH CHANGE_TRACKING AUTO
GO

Open in new window

And then use that view (in place of table above) when searching....
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 ')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_7967119Author Commented:
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?
0
Member_2_7967119Author Commented:
It took a time to return the results without the FT Search.
0
Mark WillsTopic AdvisorCommented:
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.
0
Member_2_7967119Author Commented:
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'))
0
Member_2_7967119Author Commented:
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'))
0
Mark WillsTopic AdvisorCommented:
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 ?
0
Member_2_7967119Author Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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.
0
Member_2_7967119Author Commented:
Thank you  for all the advices and suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.