Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Defining a Full Text index search on a view

Author  Topic 

Corobori
Posting Yak Master

105 Posts

Posted - 2009-03-05 : 20:27:09
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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 21:30:04
Can you try running this...instead of using the wizard

CREATE UNIQUE INDEX idxSitios2009 ON vwSitiosSchema2009 (iRowNumber)
GO
CREATE FULLTEXT INDEX ON dbo.vwSitiosSchema2009 (YourColumnName)
KEY INDEX idxSitios2009
WITH CHANGE_TRACKING AUTO

Go to Top of Page

Corobori
Posting Yak Master

105 Posts

Posted - 2009-03-06 : 21:50:09
It didn't work that way anyway because the key I defined was allowing null value.
I eventually "built" a unique key "manually" like this

ISNULL(CAST(dbo.tblSite.S_Id AS nvarchar(20)) + CAST(dbo.tblSubCategory.Sc_Id AS nvarchar(20)) + CAST(dbo.tblSiteCity.Sy_C_Id AS nvarchar(20)) + CAST(dbo.tblSiteSection.Ss_Sc_Id AS nvarchar(20)) + CAST(dbo.tblSubCategory.Sc_Mc_Id AS nvarchar(20)),1) AS sKey,


and used this "sKey" to build the index and eventually the Full Text index

jean-luc
www.corobori.com
Go to Top of Page
   

- Advertisement -