Defining a Full Text index search on a view

• Corobori

  Ten Centuries

  Points: 1316

  More actions

  March 5, 2009 at 6:31 pm

  #126851

  I am trying to define a Full Text index on an indexed view.

  I managed to define a unique and clustered index on that view

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE VIEW [dbo].[vwSitiosSchema2009]

  WITH SCHEMABINDING

  AS

  SELECT dbo.tblSite.S_Id, dbo.tblSite.S_Name, dbo.tblSiteCity.Sy_C_Id, dbo.tblSiteSection.Ss_Sc_Id, dbo.tblSubCategory.Sc_Mc_Id, dbo.tblSiteDesc.Sd_LanguageId, dbo.tblSite.S_Display, CASE WHEN S_Offline = 1 THEN '* ' + dbo.tblSiteDesc.Sd_Title ELSE dbo.tblSiteDesc.Sd_Title END AS sTitle, dbo.tblSiteDesc.Sd_Desc, dbo.tblSite.S_OffLine, dbo.tblSiteDesc.Sd_Title, dbo.tblSubCategory.Sc_Id, dbo.tblSite.S_Logo, dbo.tblSite.S_Foto, dbo.tblSite.S_Premium, dbo.tblSite.S_PremiumDate, dbo.tblSiteSection.Ss_Monto, dbo.tblSite.S_Monto, dbo.tblCity.C_R_Id, dbo.tblCity.C_Name, dbo.tblSubCategory.Sc_Desc_Spa, ROW_NUMBER() OVER (ORDER BY S_Id DESC) AS iRowNumber FROM dbo.tblSite INNER JOIN dbo.tblSiteSection ON dbo.tblSite.S_Id = dbo.tblSiteSection.Ss_S_Id INNER JOIN dbo.tblSiteCity ON dbo.tblSite.S_Id = dbo.tblSiteCity.Sy_S_Id INNER JOIN dbo.tblSubCategory ON dbo.tblSiteSection.Ss_Sc_Id = dbo.tblSubCategory.Sc_Id INNER JOIN dbo.tblSiteDesc ON dbo.tblSite.S_Id = dbo.tblSiteDesc.Sd_Id INNER JOIN dbo.tblCity ON dbo.tblSiteCity.Sy_C_Id = dbo.tblCity.C_Id WHERE (dbo.tblSiteDesc.Sd_LanguageId = 5) AND (dbo.tblSite.S_Display = 1)

  GO

  CREATE UNIQUE CLUSTERED INDEX idxSitios2009 ON

  vwSitiosSchema2009 (S_Id, Sy_C_Id, Ss_Sc_Id,Sc_Mc_Id)

  When running the Define Full Text Index wizard I can't go very far. The wizard doesn't show the index in the "Unique index" drop down list. Further down I can see that "A unique column must be defined on this table/view."

  Well actually when I define a unique column called iRowNumber creating a unique index on this column

  CREATE UNIQUE CLUSTERED INDEX idxSitios2009 ON vwSitiosSchema2009 (iRowNumber)

  works and the index does show up in the drop down but this way I am getting a message at the end of the wizard that "idxSitios2009" is not a valid index to enforce a full-text search .... error 7653

  I suppose I am missing something somewhere here

  
  Jean-Luc
  www.corobori.com
  

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  March 6, 2009 at 6:21 am

  #954811

  I am not aware of such a limitation but it would seem that indexes on computed columns are not supported by FTS.

  Is there another unique column that you can use (e.g. S_Id which is used in the definition of iRowNumber)?

  You can specify descending order for the column in the index definition.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com