SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Defining a Full Text index search on a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 03/05/2009 :  20:27:09  Show Profile  Visit Corobori's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 03/05/2009 :  21:30:04  Show Profile  Reply with Quote
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

Chile
105 Posts

Posted - 03/06/2009 :  21:50:09  Show Profile  Visit Corobori's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000