Hi, I am trying to create a full text index on a view and getting following error 'A unique column must be defined on this table/view'. My tables are.. And this is how i am creating view CREATE VIEW [dbo].[wjt_ProductSearch] AS SELECT dbo.wjt_Products.ProductID, dbo.wjt_Products.AddedDate, dbo.wjt_Products.AddedBy, dbo.wjt_Products.DepartmentID, dbo.wjt_Products.Title, dbo.wjt_Products.Description, dbo.wjt_Products.SKU, dbo.wjt_Products.UnitPrice, dbo.wjt_Products.DiscountPercentage, dbo.wjt_Products.UnitsInStock, dbo.wjt_Products.SmallImageUrl, dbo.wjt_Products.FullImageUrl, dbo.wjt_Products.Votes, dbo.wjt_Products.TotalRating, dbo.wjt_Departments.Title AS DepartmentTitle FROM dbo.wjt_Products INNER JOIN dbo.wjt_Departments ON dbo.wjt_Products.DepartmentID = dbo.wjt_Departments.DepartmentID I do not have much knowledge about T-SQL. I am unable to figure out where is the problem. Help will be really appreciated. Thanks.
[quote user="preethi"] Have you defined a primary key/Unique key on DepartmentID on the wjt_Departments table? [/quote] Yes DepartmentID on wjt_Departments table is PK.
Sorry For misleading you by asking that question. It should be about dbo.wjt_Products.ProductID. To create a fulltext index on a view, you need to have a unique index created on the view. Please read abut Indexed view to make sure whether you can create an index on it. (There are lot of conditions come into play when it comes to indexed views. IF you haven't taken any precausions, you may endup in recreating the base tables in order to create the indexed view) If not, create the full text indexes of the base tables and use them. Hope this helps
Thanks bro....I have just read an article on msdn related to Indexed view. To be honest it looks tough to me because i do not have much T-SQL knowledge. Most of the terms were unknown to me on that article. So i decided to drop the idea of using view and as you said i am using full text index directly on the table now. Next Sunday i will go through this article again and try to create indexed view for my requirement. Thanks again....